Splitting Excel sheets can be a daunting task when you have got a bunch of workbooks to split. In this article, I will share with you the VBA code to split Excel sheets into separate workbooks with a click.
If you are not familiar with using VBA, you might want to go with the first method.
If you would like to step out of your comfort zone, you might try the one with VBA.
Let’s look at the following example.
Here is a workbook with four tabs, which are “Jan 2021”, “Feb 2021”, “Mar 2021”, “Apr 2021”.
Each one of them contains the sales record for the specified month.
There are two columns within the worksheet – “Fruit” and “Sales”.
What we would like to do
To split those four sheets into 4 different workbooks
You might also be interested in Edit The Same Cell In Multiple Excel Sheets
Split Excel sheets into separate workbooks by Move or Copy
Step 1&2 : Right click the tab and select “Move or Copy”
Step 3 : Select (new book) from the “to book:” drop down list and press “OK”
If you would like to keep the original Excel file, you can check the “Create a copy” box.
Here I do not want to keep the original one so I left the box unchecked.
Step 4 : Save the new Excel file as a new document
You might also be interested in How To Change The Default Number Of Sheets In Excel
Split Excel sheets into separate workbooks by VBA
This one is a much more efficient way.
Simple insert and run the code and you are off to go.
Read How to Insert & Run VBA code in Excel – VBA101 if you forget how to insert VBA code in Excel.
Private Sub splitsheets() Dim ws As Worksheet Dim thisBook As Workbook Set thisBook = ActiveWorkbook For Each ws In MyBook.Sheets ws.Copy 'split Excel sheets into separate workbooks ActiveWorkbook.SaveAs Filename:=thisBook.Path & "\" & ws.Name, FileFormat:=xlNormal Next MsgBox "Splitting finished" End Sub
Those four different sheets will be saved into a separate workbook. The workbook will be named by the name of the worksheet. You don’t even need to save and name the workbook by yourself.
Hungry for more useful Excel tips like this? Subscribe to our newsletter to make sure you won’t miss out on any of our posts and get exclusive Excel tips!