Copy a module from one workbook to another

 2008-04-26    VBE    2    162

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