Copy Worksheet and its code into another workbook

Question

I want to copy an excel worksheet from one workbook into another with all of its VBA Code. Every time I try to "Move or Copy Sheet" the sheet copies, but none of the VBA Modules transfer into the new workbook. Is there a way to do this?

Is the code in the sheet object or a module? If they're not actually in the sheet, you'll have to copy it over by dragging the module from your sourcebook to your destination workbook. — dwirony
Some of it is in the sheet object but I also have three modules that contain code as well. — mdp476
I would do a template which contains your VBA Code and create an extra workbook containing the macro that could copying the template (filled with data I guess) — Chris

donPablo donPablo · Accepted Answer · 2019-02-05T20:43:12

I have used something like this in the past. Your mileage may vary. It needs to be adjusted to refer to TWO different Workbooks. It has not been tested with recent MSOffice. I do not know if it handles code in a sheet object.

Option Explicit
    Dim sExportLocation As String
    sExportLocation = "C:\myTempExport\" 'Do not forget the closing back slash! ie: C:\Temp\

Public Sub DoExportImport
    ExportTheModules
    ImportDatabaseObjects
End Sub    

Public Sub ExportTheModules
     '===============================================================================
     ' Name:    DocDatabase Purpose: Documents the database to a series of text files
     ' Author:  Arvin Meyer       ' Date:    June 02, 1999
     ' Comment: Uses the undocumented [Application.SaveAsText] syntax
     '          To reload use the syntax [Application.LoadFromText]
     '      Modified to set a reference to DAO 8/22/2005
     '===============================================================================
    Dim dbs As DAO.Database,  cnt As DAO.Container,  doc As DAO.Document

    Set cnt = dbs.Containers("Modules")
    For Each doc In cnt.Documents
        Application.SaveAsText acModule, doc.Name, sExportLocation & "Mods_" & doc.Name & ".txt"
    Next doc
End Sub

Public Sub ImportDatabaseObjects()
    On Error GoTo Err_ImportDatabaseObjects
    Dim db As Database      'Dim db As DAO.Database
    Dim td As TableDef, d As Document, c As Container  
    Set db = CurrentDb()

    Dim sFN As String, sName As String, sList() As Variant
    sList = Array("Module_", acModule)     ' Array("Module_", acModule, "Form_", acForm, "Query_", acQuery)
    sFN = Dir(sExportLocation & sList(0) & "*.txt")
    Do While sFN <> ""
        sName = Replace(sFN, sList(0), "")
        sName = Replace(sName, ".txt", "")
        Debug.Print sFN, , sName
        Application.LoadFromText sList(1), sName, sExportLocation & sFN
        sFN = Dir
    Loop

    Exit Sub

Err_ImportDatabaseObjects:
    MsgBox Err.Number & " - " & Err.Description
    Set c = Nothing
    db.Close
    Set db = Nothing    
End Sub

Copy Worksheet and its code into another workbook

1 Answers