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() :
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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!