Copy a module from one workbook to another

 2008-04-26    VBE    2    103

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

Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.

OPE | 2010-01-15 08:41:05 (GMT)

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.

Arun | 2010-01-14 19:35:13 (GMT)

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.

Arun Pillai