How do I copy macro into new workbook that was created automatica.

[Guest]

I am creating a new workbook with a macro. I want the macro to be copied to
the new workbook too automatically so I can run that macro again from the new
workbook.

 

[Tom Ogilvy]

Look at Chip Pearson's page on working with the VBE with code
http://www.cpearson.com/excel/vbe.htm

 

[CodeSponge]

Here how I do it with one of my projects.


With Workbooks("PERSONAL.XLS").VBProject
..VBComponents("FollowUpSupport").Export
("h:\Macros\FollowUpSupport.bas")
..VBComponents("ProjectSupport").Export
("h:\Macros\ProjectSupport.bas")
..VBComponents("PSSupport").Export ("h:\Macros\PSSupport.bas")
..VBComponents("PSLogin").Export ("h:\Macros\PSLogin.frm")
End With

With neoBook
..VBProject.VBComponents.Import "h:\Macros\FollowUpSupport.bas"
..VBProject.VBComponents.Import "h:\Macros\ProjectSupport.bas"
..VBProject.VBComponents.Import "h:\Macros\PSSupport.bas"
..VBProject.VBComponents.Import "h:\Macros\PSLogin.frm"
..VBProject.References.AddFromFile
"C:\WINDOWS\system32\vbscript.dll\3"
..VBProject.References.AddFromFile
"C:\WINDOWS\system32\shdocvw.dll"

..SaveAs "H:\EXCEL\FollowUpList.xls", xlWorkbookNormal
End With
End Sub

 

[Dave Peterson]

Another option would be just to leave the code in its own workbook.

Then have that "macro workbook" open whenever you need to run the macro.

(I've found that it's easier to make a change in one workbook, than to find all
the workbooks and update all those.)