How do I duplicate an excel workbook and all of the code inside it-CodePudding

I am having a bit of brain freeze moment. I need to copy a workbook including all of its VBA code. I then need to delete from the workbook a couple of sheets. I know that I can just create a new workbook and copy the sheets to that new wb however I need the code for the entire wb to go with it.

I also know that I could do a workbook save copy as.
However it will be random people saving it to their computer and so how do I know how to refer to this book in order to then delete the sheets?

I feel like it is so easy, but I don't know enough to figure it out.
Thanks for your help!

CodePudding user response：

I inserted this code in ThisWorkbook in the VB Editor.

I tested it and it works for me.

Change "C:\Users\user\Documents\HP Laptop\Documents\Documents\Jobs\DIT\IDMB\Stack Overflow.xlsm" to your document and Sheet2 and Sheet3 to meet your own needs.

Then when your colleagues open their copies of your document, it will automatically delete the sheets that they don't need.

Option Explicit

Private Sub Workbook_Open()
'Private Sub Test()

Dim WS As Worksheet, wsBlank As Worksheet
Dim wb As Workbook

Set wb = ThisWorkbook

If wb.FullName <> "C:\Users\user\Documents\HP Laptop\Documents\Documents\Jobs\DIT\IDMB\Stack Overflow.xlsm" Then
    For Each WS In wb.Sheets
        If WS.Name = "Sheet2" Or WS.Name = "Sheet3" Then
            Application.DisplayAlerts = False
            WS.Delete
            Application.DisplayAlerts = True
        End If
    Next
    
    Application.DisplayAlerts = False
    wb.Save
    Application.DisplayAlerts = True
End If

End Sub

CodePudding user response：

I believe you are worrying far to much about the fact that the file you want to copy is a workbook with all its macros, ...

You can, as far as the copying is concerned, simply treated as any other file, like this:

Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile "C:\Temp_Folder\Test.txt", "C:\Temp_Folder\Test2.txt2"

This works for any kind of file (Excel files, included). Once you have done this, you can open the copied file and remove some sheets and so on.