Erlandsen Data ConsultingCopy a module from one workbook to another
With the macro below you can copy a module from one workbook to another:
Sub CopyModule(SourceWB As Workbook, strModuleName As String, TargetWB As Workbook)
' requires a reference to the Microsoft Visual Basic Extensibility library
' copies a module from one workbook to another
' example: CopyModule Workbooks("Book1.xls"), "Module1", Workbooks("Book2.xls")
Dim strFolder As String, strTempFile As String
strFolder = SourceWB.Path
If Len(strFolder) = 0 Then strFolder = CurDir
strFolder = strFolder & ""
strTempFile = strFolder & "~tmpexport.bas"
On Error Resume Next
SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
TargetWB.VBProject.VBComponents.Import strTempFile
Kill strTempFile
On Error GoTo 0
End Sub
Hi Expert,
I was trying to use you solution in getting some of my problems resolved. I was trying to optimize the code to “Copy a module from one workbook to another”. I have inculded the “Microsoft Visual Basic Extensibility library” in my Excel 2007 however when I am trying to run the code it throws error “Runtime error ’9′
Error message: Script out of range.
Unable to proceed further. Can pls give some suggestion and help me out.
Thanks,
Arun Pillai
The error message you receive suggests that you have put invalid information into the procedure and that the macro can’t find the workbook(s) or module you are trying to copy.