Use Excel VBA to Separate Worksheets to Files

Create any number of Excel files at the click of a button using Excel VBA

Click here to download the STARTER example file used in the videos.
Click here to download the COMPLETED example file used in the videos.

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!’.

Excel VBA Message Boxes

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


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’.

Video 3 – Applying a Loop to Scale Up the Code

We are dealing with a ‘repeated’ set of instructions since we want Excel to do the same thing to multiple worksheets. The concept of ‘repetition’ can only mean one thing – loops. A loop is a powerful programming technique that repeats a set of instructions any number of times. But, like any powerful beast, loops require careful control!

In the final video, we look at how to set up and control the loop so that it ‘works though’ the database, pointing to the next row each time the loop iterates. This involves another Tiger favourite – the Excel VBA offset command. Loops and offset really are like fish and chips: they work beautifully together. Master their interaction to get a mind-boggling amount of work done at the click of a button. In this example, we set up the loop to work through the worksheets in the file, copy each to a new file, and to save each under an appropriate file name. A task that would take a few minutes, and cause some frustration, is now complete in just a few seconds.

What did you think about the Use Excel VBA to Separate Worksheets to Files videos series? Did you manage to save some time, and did you feel the power of the VBA techniques? We would love to know how you get on – contacts below.

For regular spreadsheet hints and tips and more on the #ExcelRevolution:

Contact us here.

This entry was posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized. Bookmark the permalink. Comments are closed, but you can leave a trackback: Trackback URL.