Have exported a module (macro) now need help importing it into another workbook

[RogerDodger]

This forum is great. Thanks for taking the time to read my question. I am making a copy of a worksheet contained in the host workbook, which creates a new workbook (export workbook). I save the export workbook and have two workbooks open. I activate the host workbook and export a module from it then activate the export workbook and import the module into it however it is importing into the host not the export worbook. I have a feeling activating a workbook does not necessarily change the activeVBproject however this is just a guess. Here is the code I have so far. Thanks to all for your time and effort.

Workbooks(Host).Activate
ActiveWorkbook.VBProject.VBComponents("ResetXFER1").Export ("C:\ResetXFER1.bas")
Workbooks(NWBN).Activate: Sheets("Upload_Template").Select '<-----maybe this is not activating the VBproject???
Application.VBE.ActiveVBProject.VBComponents.Import ("C:\ResetXFER1.bas



In case it helps, here is a larger snippet of code...

Sub ExportReport()

'010 Miscl presets
On Error GoTo ExportdbErrorTrap
Host = ActiveWorkbook.Name
Application.StatusBar = "Exporting..... for job " & C14 - 4


'020 Append text to filename
Append1 = Now
Append1 = Replace(Append1, "/", "-") 'make filename legal
Append1 = Replace(Append1, ":", ".")
NWBN = "ExportName " & Append1 & ".xls"

'040 Create new workbook
On Error GoTo 0
Sheets("Upload_Template").Select
Sheets("Upload_Template").Copy
ActiveWorkbook.SaveAs (NWBN)


'050 Export and Import Macro (for new workbook)
Workbooks(Host).Activate
ActiveWorkbook.VBProject.VBComponents("ResetXFER1").Export ("C:\ResetXFER1.bas")
Workbooks(NWBN).Activate: Sheets("Upload_Template").Select
Application.VBE.ActiveVBProject.VBComponents.Import ("C:\ResetXFER1.bas")
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub

 

[RogerDodger]

I read the FAQ and stumbled on the bit about cross-posting. I never consider cross posting would be an issue but I can see it might be. This was posted simply in order to get a answer as fast as possible. The other post is here:

http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads

I'll not cross post again now that I understand it is an issue and have asked the administrator to remove this post.

Sincerely,

Roger