Excel Tip of the Week #223 - VBA case study: Splitting a workbook by sheet

Hello and welcome back to the Excel Tip of the Week.  This week, we have a Developer level post in which we are going to work through the VBA code required to take a workbook and cut it into one file for each sheet, and save all the resulting files.

Concept one - active workbooks and worksheets

Excel at any time has an active workbook and active worksheet.  This is typically the currently displayed workbook / sheet.  You can operate on these using ActiveWorkbook.[action] and ActiveSheet.[action].

You can store the current workbook / worksheet in a variable with Set BLAH = ActiveWorkbook / ActiveSheet.

You can change the active item to a stored value with BLAH.Activate.

Concept two - Save As

You can run the "Save As" dialogue from VBA.  There are a lot of extra variables for e.g. setting passwords and the like, but the basic method is:


If no folder path is specified, the default is to use the current location.  If you're running a macro within the workbook you're working on, that might be fine, but if your macro is stored in Personal.xlsb or an Add-In, that's going to be no good.  To fix that, you will need to specify the file path where you want the file saved as well.

The code

We are going to create a loop that will take each worksheet in turn, copy it to a new workbook, save the new workbook (using the sheet name as the filename), and then move to the next sheet.  This version leaves the new files open but you could close them if you wanted to.

Because our code is designed to sit in Personal.xlsb and be used with a keyboard shortcut, we need to make sure that the new files are saved in the folder where the original file is.  We also aren't going to delete the original file as a precaution in case anything goes wrong (e.g. there is already a file in the folder with the same name as one of the new ones we are trying to create).

Here's our full code:

Sub Splitter()
Dim i As Integer
Dim FPath As String
Dim ParentFile As Workbook

FPath = ActiveWorkbook.Path
Set ParentFile = ActiveWorkbook

For i = 1 To ActiveWorkbook.Sheets.Count
    ActiveWorkbook.SaveAs (FPath & Application.PathSeparator & ActiveSheet.Name)
Next i

End Sub

Just a couple of explanatory points:

  • The FPath variable is used to store the folder location of the active workbook
  • The ParentFile variable is used to identify the original workbook, so we can switch back to it after each iteration of the loop
  • Application.PathSeparator is the character use to distinguish elements of a file path.  On Windows, we could just use a "\" here, but using this will make sure our macro works on other systems as well.

And that's it!

Previous post - Revisiting working with multiple screens
TOTW index 
Next post - Revisiting SUMPRODUCT

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.