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() :

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:

Yup, it works!

Tagged with: , , ,