Use Excel VBA to Separate Worksheets to Files
Create any number of Excel files at the click of a button using Excel VBA
We receive many emails from people who spend too much time moving Excel worksheets to separate files. This is a common task: if you are creating reports for students in a school or payslips for employees in a company, for example, you have to do this or something similar. It’s time-consuming, takes lots of mouse clicks and leaves us thinking ‘there must be faster way!’.
One of the key themes of our training is that a small set of VBA techniques can transform how you go about your work. We say they deliver ‘disproportionate benefit’. Some of these techniques are: using the the macro recorder; using VBA variables and applying loops. With this video series, you will learn how to get this task done at the click of a button and improve these critical skills.
So, how can we use Excel VBA to separate worksheets to files? Let’s go! Don’t forgot to download the Excel file (above) and work along with Chris…
Video 1 – Using the Macro Recorder to Get Started
‘INTELLIGENCE IS KNOWING WHAT TO DO WHEN YOU DON’T KNOW WHAT TO DO’.
We like this aphorism at Tiger. For many professionals, applying code to this task is a situation in which they would not know what to do – ‘how should I get started? I have no idea’. To avoid giving up, we have to tap into ‘metaskills’ – mental resources that guide our actions when we are stuck, and that enhance or limit our capacity to learn. How does this translate into Excel? If you have no idea how to begin a coding task, what is the required metaskill? The macro recorder, of course!
The macro recorder allows us to do something in Excel and record the corresponding VBA code. So, for this task, we hit ‘play’ to start the recorder, and then copy the first worksheet to a new file; once we stop the recorder (Chris almost forgets!) we can review the code in the VBA editor. This is pure gold for the amateur coder; even if you have no coding knowledge, you can ‘create’ code for whatever you are trying to do, then try to tune it up and get it working. Try it.
Video 2 – Tweaking Recorded Code, Introducing a Variable
In the second video, we review the code in the VBA editor and tweak it to get it working for our purposes. One problem often encountered when creating multiple files is that of file names; we cannot save two files with the same name to the same location, as Excel helpfully tells us!
We resolve this by introducing a VBA variable to store the desired file name – generated in the original, origin workbook – and to apply it to the new workbook. By the end of the video we have a routine to separate a single worksheet to a single file; though this in itself is not very exciting, it is an important intermediary step that allows us, in the next video, to apply powerful Excel techniques to quickly ‘scale up’ the code to work for multiple worksheets and files. We think ‘get it to work once, accurately and robustly, then apply the loop’.