#454 - Revisiting splitting workbooks with VBA

Why VBA is (sometimes) the best option

With Power Query now an integral part of Excel, and Power Automate available to perform more advanced automated processes, the question is often asked whether VBA has a place in the modern Excel practitioner’s toolkit. The short answer is yes, but only in very specific circumstances; there remain some functions that VBA is just much better at handling, though these are becoming increasingly few and far between. Mostly, VBA is useful because it can make spreadsheets interactive in a way that other solutions – including Office Scripts for Excel Online – simply cannot. But it’s also useful when you need to perform manipulations of Excel Workbooks, such as the one we’re exploring here. Having to save individual sheets as separate files is a not uncommon task; VBA makes it quick and easy.

Remember, before embarking on using VBA, you should ensure you are comfortable working in Visual Basic, and take a backup of your file. We’re deliberately not covering the basics here but you can look back at all of our VBA and macro-related tips on our Index page.

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. In VBA, you can operate on these using ActiveWorkbook.[action] and ActiveSheet.[action].

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

You can change the active item to a stored value with VAR.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:

ActiveWorkbook.SaveAs(Filename)

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.

Concept 3 – Looping 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. In this version, the code closes the new workbooks once saved, but the “Activeworkbook.Close” line of code could be commented out or removed entirely.

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
Application.ScreenUpdating = False
For i = 1 To Activeworkbook.Sheets.Count
  ParentFile.Activate
  Activeworkbook.Sheets(i).Copy
  Activeworkbook.SaveAs (FPath & Application.PathSeparator & ActiveSheet.Name)
  Activeworkbook.Close
Next i
Application.ScreenUpdating = True
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.
• Application.ScreenUpdating = False is used to prevent “flickering” as Excel repeatedly opens, saves and closes new workbooks. Note it has to be set to “True” at the end of the routine to return to the appropriate setting.

And that's it! Unless…

Concept 4 – Bonus Logic

I recently encountered just this scenario where I needed to split out a workbook for different teams to populate information and return to me. Except I had one extra step to deal with: the data was all in a single sheet to start with and so needed to be split out. Fortunately, this step can now easily be done using the FILTER function, as explained in Tip #443, but it creates a problem: you will be left with one “master” sheet that you don’t want to save to a new workbook. This can easily be solved with a simple pop-up prompt to specify the name of this “master” sheet, that the code then checks for and bypasses as part of the loop:

Dim MasterSheet As String
MasterSheet = Application.InputBox(prompt:="Which worksheet would you like to exclude? Leave blank if none", Title:="Master sheet", Type:=2)
For i = 1 To ActiveWorkbook.Sheets.Count
  If MasterSheet <> ActiveWorkbook.Sheets(i).Name Then
       ParentFile.Activate
       ActiveWorkbook.Sheets(i).Copy
       ActiveWorkbook.SaveAs (FPath & Application.PathSeparator & ActiveSheet.Name)
       ActiveWorkbook.Close
  End If
Next i