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.

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.

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 .

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

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

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

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.

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.

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