How to split sheet into multiple separate Excel files

Having multiple sheets will increase the file size, especially when you add add-ins or add VBA macro code in Excel. Therefore, splitting sheets into multiple files is a reasonable solution in case users only want to get data in a certain sheet.

How to split sheet into multiple separate Excel files Picture 1

Split sheet into separate files

How to split sheet into multiple Excel files

Step 1: First, you need to check if the Excel you are using has the Developer tab. If not, you can display the Developer tab  according to the instructions here.

Next, open the Excel file with multiple sheets. Suppose the image below shows you have an Excel file with 5 different sheets.

How to split sheet into multiple separate Excel files Picture 2

Step 2: Press the key combination ALT + F11 to open the VBA program in Excel, or click on the Developer tab -> then select Visual Basic .

How to split sheet into multiple separate Excel files Picture 3

Step 3: Then, the VBA interface appears -> click on the Insert tab -> then select the Module section to open the VBA code entry interface.

How to split sheet into multiple separate Excel files Picture 4

Next, copy the code below into the Module interface above.

Sub Splitbook()

'Updatedby20140612

Dim xPath As String

xPath = Application.ActiveWorkbook.Path

Application.ScreenUpdating = False

Application.DisplayAlerts = False

For Each xWs In ThisWorkbook.Sheets

xWs.Copy

Application.ActiveWorkbook.SaveAs Filename:=xPath & "" & xWs.Name & ".xls"

Application.ActiveWorkbook.Close False

Next

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

How to split sheet into multiple separate Excel files Picture 5

Step 4: Click Run on the toolbar to run or press F5 on the keyboard to run the code you just added above.

How to split sheet into multiple separate Excel files Picture 6

After the code execution is complete, you open the folder containing the original Excel file -> here, you will see each new file separated from the sheets will be located here. If your Excel file uses calculation formulas, all calculation formulas and data in each sheet will still be guaranteed and kept intact.

How to split sheet into multiple separate Excel files Picture 7

You can open the split file to check. If there is a notification as shown below -> click Yes to agree and the file will be opened normally.

How to split sheet into multiple separate Excel files Picture 8

So that's the end of the Free Download tutorial on splitting sheets into multiple Excel files. The steps for you to follow are really simple, right? The hardest part is to have the code to use, you can copy the code that TipsMake has created, and you simply copy them into the Module and run them.

In addition, you can add more Sheets in Excel or you can even merge multiple Excel files into 1 File  in Excel quite easily! All instructions are available on TipsMake, please refer to the implementation and follow it. Good luck!

Samuel Daniel

Update 17 December 2024