How to Split Excel Sheets into Separate Workbooks

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.

Sample

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

How to split Excel sheets into separate workbooks — Four different worksheets in the workbook

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

Result

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!