Macro to move all sheets in book

Posted by Ed J. on February 20, 2001 8:22 AM

I want to write a macro that will move all the sheets from a book I select to the current book. When I record a macro, individual sheets are recorded. I want every sheet in the selected book, generically (the number of sheets in the books and the names are not consistent). Anybody have a hint on this one?

Posted by Celia on February 20, 2001 3:50 PM

Ed
Workbooks("Custom Workbook1").Sheets.Copy Before:=Workbooks("Custom Workbook2").Sheets(1)
Celia

Posted by Ed J. on February 21, 2001 8:05 AM

Thanks Celia - Now...

Now I want to copy a Macro (Module 1) from Custom Workbook1 over to Custom Workbook2. I can do it with export and import mannually, but can't seem to get the VBA code right to do it within the Macro - and it doesn't record when I do it manually (darn it!)

Any Clues?

Posted by Celia on February 21, 2001 3:16 PM

Re: Thanks Celia - Now...

Sub CopyModule()
Workbooks("Custom Workbook1").VBProject.VBComponents("Module1") _
.Export Filename:="C:\My Documents\new.bas"
Workbooks("Custom Workbook2").VBProject.VBComponents _
.Import(Filename:="C:\My Documents\new.bas").Name = "NewModule"
Kill "C:\My Documents\new.bas"
End Sub

Celia

Posted by Celia on February 21, 2001 3:51 PM

Also....

To use the above code, you must have a reference set to the VBA Extensibility library.
In the VBA editor, go to Tools, References, and check "Microsoft Visual Basic For Application Extensibility".
celia