Excel VBA - Copy Workbook into a new Workbook with the macros

Question

So I have a worksheet that generates a chart type of thing using information on 2 other worksheets. On It I have an extract button which should copy the entire workbook into a new workbook whilst making the sheets where the data is pulled from invisible to the user. My issue is, the chart worksheet has other features which require macros to be run, for example buttons that hide some of it etc. The issue is I cannot find whether its actually possible to copy through macros from a workbook into the new copied workbook? Anyone have an answer to this and if so, how would you do this? Here is the code I currently have which copies the workbook into a new workbook:

Sub EWbtn()

Dim OriginalWB As Workbook, NewCRCWB As Workbook

Set OriginalWB = ThisWorkbook
Set NewCRCWB = Workbooks.Add


OriginalWB.Sheets("Generator").Copy Before:=NewCRCWB.Sheets("Sheet1")
OriginalWB.Sheets("Module Part Number Tracker").Copy Before:=NewCRCWB.Sheets("Generator")
OriginalWB.Sheets("CRC").Copy Before:=NewCRCWB.Sheets("Module Part Number Tracker")

Application.DisplayAlerts = False
NewCRCWB.Worksheets("Generator").Visible = False
NewCRCWB.Worksheets("Module Part Number Tracker").Visible = False
NewCRCWB.Worksheets("Sheet1").Delete
Application.DisplayAlerts = True

End Sub

I'd make a copy of the original Excel file (`SaveCopyAs`) and delete any sheets that aren't needed. — Darren Bartrup-Cook, Jul 24 '17 at 08:13

score 1 · Answer 1 · answered Jul 24 '17 at 08:27

I'd take a copy of the original file and delete/hide sheets from that.
All code is copied over as part of the save.

Sub Test()

    Dim wrkBk As Workbook
    Dim sCopyFileName As String
    Dim wrkSht As Worksheet

    sCopyFileName = "C:\MyFolderPaths\Book2.xlsm"

    'Create copy of original file and open it.
    ThisWorkbook.SaveCopyAs (sCopyFileName)
    Set wrkBk = Workbooks.Open(sCopyFileName)

    'wrkbk.Worksheets does not include Chart sheets.
    'wrkbk.Sheets would take into account all the types of sheet available.
    For Each wrkSht In wrkBk.Worksheets
        Select Case wrkSht.Name
            Case "Generator", "Module Part Number Tracker"
                wrkSht.Visible = xlSheetVeryHidden
            Case "CRC"
                'Do nothing, this sheet is left visible.
            Case Else
                Application.DisplayAlerts = False
                wrkSht.Delete
                Application.DisplayAlerts = True
        End Select
    Next wrkSht

    wrkBk.Close SaveChanges:=True

End Sub

score 0 · Accepted Answer · answered Jul 24 '17 at 08:12

I managed to find an answer to my question.. This code works fine however you need to add "Microsoft Visual Basic for Applications Extensibility 5.x" as a reference via Tools -> References. Here is the code:

Dim src As CodeModule, dest As CodeModule

Set src = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
Set dest = Workbooks("Book3").VBProject.VBComponents("ThisWorkbook") _
.CodeModule

dest.DeleteLines 1, dest.CountOfLines
dest.AddFromString src.Lines(1, src.CountOfLines)

Credit: Copy VBA code from a Sheet in one workbook to another?

Excel VBA - Copy Workbook into a new Workbook with the macros

2 Answers2