VBA – Copying Excel worksheet to a new workbook without leaving the old Excel reference

Copying excel worksheets around is actually a piece of cake for a VBA developer (or so they/we think). Mainly because you can record the actions and then “learn” from them, editing a bit the code and etc. Well, there is one tiny but, that actually is not that tiny – if you do it this way, you may somehow end with having the reference of the source workbook in the new workbook like this:

And this is painful to watch. Of course, you may consider running some additional code to replace the “[SourceWorkbook.xlsb]” with something, but this is a bit painful. This is why, I have named the sub, producing the workbook above CopyWorksheetsUgly() :

Sub CopyWorksheetsUgly()
    
    Dim wks As Worksheet
    Dim wkb As Workbook
    Set wkb = Workbooks.Add
    wkb.Title = "NewCopyWithReference.xlsx"
    
    For Each wks In ThisWorkbook.Worksheets
        wks.Copy after:=wkb.Worksheets(Worksheets.Count)
    Next wks
    
End Sub

In order to get the beautiful part, looking like the picute below, a few more steps are needed:

Actually, just one – all the worksheets should be copied at once. For this part, a few more lines are needed, saving the names of the worksheets that we will export to an array and copying all of them at once:

Sub CopyWorksheetsBeautiful()
    
    Dim wks As Worksheet
    Dim wkb As Workbook
    Set wkb = Workbooks.Add
    wkb.SaveAs "NewCopyWithReference.xlsx"
    
    ReDim myArr(ThisWorkbook.Worksheets.Count - 1)
    
    Dim i As Long
    For i = 0 To ThisWorkbook.Worksheets.Count - 1
        myArr(i) = ThisWorkbook.Worksheets(i + 1).Name
    Next i
    
    ThisWorkbook.Worksheets(myArr).Copy wkb.Worksheets(Worksheets.Count)
       
End Sub

Yup, it works!