Macro in excel

M

mrbalaje

I am using Record a macro option in Excel. I had successfully recorded a
macro in which the reports that I wanted from various sheets like
sheet1,sheet2,.... are copied in one single sheet.

I want that single sheet which contain all the reports that I needed to be
in a new excel without other sheets and the macros that I have created.

The option i have tried while recording a macro is that, I just right-click
on the sheet tab and then create a copy and then move to a new workbook.

But it doesn't work. Please let me know how I can extract that particular
sheet out of the excel with contain macro to a new excel without macro.
 
J

JulieD

Hi

recorded macro code doesn't "live" on the sheet - it lives in a code module
in the vbe window - you need to copy that code module into the new workbook
so,
open your workbook with the macro (i'm calling this existing.xls)
open the new workbook (i'm calling this new.xls)
in existing.xls choose tools / macro / macros, find you macro and click on
EDIT
the VBE Window will open and show you your code
down the left hand side of the screen you should be able to see the name of
existing.xls in bold and the name of new.xls in bold (if not choose view /
project explorer)
your cursor should have "module1" (or similiar) in the section under
existing.xls highlighted
click on that module,hold down your control key and drag it to new.xls - let
the control key go and then the mouse
now you should see module 1 under new.xls as well - you've copied your macro
close the VBE window and carry on as normal.
 
M

mrbalaje

Hi,

I don't want that macro coding to be copied from one excel to an another
excel.
I want only one sheet say "sheet1" from the excel which contain macro to be
extracted to an another new excel.
 
G

Gord Dibben

mrbalaje

This is the code I got by recording these steps while an existing workbook was
open.

Right-click Sheet1 tab.

Move or Copy.

Checkmark "create a copy"

Selected "New Book" from "To Book" and hit OK

Sub Macro1()
Sheets("Sheet1").Select
Sheets("Sheet1").Copy
End Sub

You can record this macro to your Personal Macro Workbook so it is available
from any workbook you have open.



Gord Dibben Excel MVP
 
Top