How do I duplicate an Excel workbook and all of the code inside it?

Question

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 create a new workbook and copy the sheets.
However I need the code for the entire workbook 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?

Welcome to SO. *how do I know how to refer to this book?*. Check [Application.ThisWorkbook property (Excel)](https://learn.microsoft.com/en-us/office/vba/api/excel.application.thisworkbook). You can link a variable too to a specific workbook with [Set statement](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/set-statement). There are millions of examples. — Foxfire And Burns And Burns, Sep 06 '22 at 12:41

score 2 · Answer 1 · answered Sep 06 '22 at 13:18

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.

Cyrus · Accepted Answer · 2022-09-07T06:27:24.980

1

You can copy this code to your new button_click routine

I tested it and it works for me.

Change Sheet3 and Sheet4 to meet your own needs.

The folder dialog code came from here.

Option Explicit

'Private Sub button_click()
Private Sub test73622125()

Dim fldr As FileDialog, fso As Object
Dim sFilename$, sItem$
Dim wb As Workbook
Dim WS As Worksheet

'create folder dialog

Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
sFilename = ""
With fldr
    .Title = "Please select a folder in which to create a copy of this workbook for a student"
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path
    If .Show <> -1 Then
        Call MsgBox("No folder selected. No copy created", vbOKOnly, "Warning")
        Exit Sub
    Else
        'the new copy must have a different name so this code can open it immediately.
        ' Excel will not open two documents with the same name simultaneously
        sFilename = .SelectedItems(1) & "\" & Replace(ThisWorkbook.Name, ".xlsm", " Copy.xlsm")
    End If
End With

'copy file, courtesy of Dominique

Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile ThisWorkbook.FullName, sFilename

'open newly copied document
Set wb = Workbooks.Open(FileName:=sFilename)

'change Sheet3 and 4 to the the sheets students should not see

For Each WS In wb.Sheets
    If WS.Name = "Sheet3" Or WS.Name = "Sheet4" Then
        Application.DisplayAlerts = False
        WS.Delete
        Application.DisplayAlerts = True
    End If
Next

'save and close new copy for student

Application.DisplayAlerts = False
wb.Close (True)
Application.DisplayAlerts = True

End Sub

edited Sep 07 '22 at 06:27

answered Sep 06 '22 at 12:47

Cyrus

356
3
14

Hello and thank you for your replies. I think I missed out a critical element when reviewing your questions. Sorry! The file will be stored on Google drive, my colleagues will download it to their computer and then click on a button in that workbook to copy it and the deletion of sheets will make it a student safe file to give out, whilst they keep the "teacher safe" file. So, I can not add a file path for the original file. sorry :/ – KaD Sep 06 '22 at 14:03
@KaD If one of our answers has solved your problem, then please accept it as the correct answer ? You can do so by clicking the large "V" tick at the top of my answer. – Cyrus Sep 06 '22 at 14:05
thanks for the info Cyrus but even though the answers given answer the Q in a generic way, they haven't quite answered mine specifically. I will click th "V" when / if its answered and i no longer need help. Thanks! – KaD Sep 06 '22 at 15:20
Our solutions solve your question as it was originally expressed above. Obviously your comment fully expressess your question. That's why it's important to take time asking a question here to ensure that all the relevant details are included at the outset. Denigrating volunteers trying to help you might not be the best approach. Nevertheless I will combine our solutions into one. If the new solution meets your needs then I would ask that you upvote Dominique's contribution too as I have done so. – Cyrus Sep 07 '22 at 05:51
I have updated my answer above with our combined solution. I hope that's ok with Dominique. – Cyrus Sep 07 '22 at 06:28

How do I duplicate an Excel workbook and all of the code inside it?

2 Answers2