Try code like

Const FILENAME = "C:\Temp\Module1.bas"
On Error Resume Next
MkDir "C:\Temp"
Kill FILENAME
On Error GoTo 0
Workbooks("Book1.xls").VBProject.VBComponents("Mod ule1").Export _
FILENAME:=FILENAME
On Error Resume Next
With Workbooks("Book2.xls").VBProject.VBComponents
.Remove .Item("Module1")
End With
Workbooks("Book2.xls").VBProject.VBComponents.Impo rt _
FILENAME:=FILENAME
Kill FILENAME


See also www.cpearson.com/excel/vbe.aspx for lots more code about
manipulating the VBA editor and code using code.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]







On Sat, 19 Dec 2009 16:46:02 -0800, Ken Hudson
wrote:

I am using Excel 2003.
I have two open workbooks: one containing a macro and another workbook with
no modules.
I want to copy Module1 from the macro workbook to the other open workbook.
I found some code that I tried to adapt but it is not working.
Can someone tell me how to fix it?
Do I need to add any specific references?

Sub CopyModule()
Dim strFolder As String
Dim strTempFile As String
Dim TargetWB As Workbook
Dim SourceWB As Workbook
Dim strModuleName As String
strTempFile = "C:\test\" & "~tmpexport.bas"
Set SourceWB = Workbooks("Book1")
Set TargetWB = Workbooks("Book2")
strModuleName = "Module1.bas"
SourceWB.VBProject.VBComponents(strModuleName).Exp ort strTempFile
TargetWB.VBProject.VBComponents.Import strTempFile
Kill strTempFile
End Sub