sometimes i just export and import the module, using something like this:

ThisWorkbook.VBProject.VBComponents("Mod_Print_Rep ort").Export Filename:=fPath &
"Mod_Print_Report.bas"

ActiveWorkbook.VBProject.VBComponents.Import Filename:=fPath &
"Mod_Print_Report.bas"
--


Gary Keramidas
Excel 2003


"Ken Hudson" wrote in message
...
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
--
Ken Hudson