Guide on how to split Excel Sheets into separate Excel files

Guide on how to split Excel Sheets into separate Excel files: In an Excel file, you can create multiple sheets to keep track of and switch between related content. However, having too many sheets can increase the file size and make opening the file slower, especially when you use add-ins or VBA macro codes. The solution at this point is to split the sheets into separate Excel files. This way, the content in the sheet is still preserved, the sheet name becomes the name of each file, and the formulas used in the sheet are not changed or result in errors. This article will guide you on how to split each sheet in Excel into separate files.

Example: Let’s say we have an Excel file containing multiple sheets as shown in the figure below. The requirement is to split those sheets into separate Excel files. Please follow the steps below to split the sheet.

Guide on how to split Excel Sheets into separate Excel files

Step 1

To split the sheet into separate Excel files, first, you need to select the Developer tab on the toolbar. Then, select Visual Basic under the Code section. Or you can use the shortcut key Alt + F11 to quickly open the VBA window.

Guide on how to split Excel Sheets into separate Excel files

Step 2

At this point, the Microsoft Visual Basic for Applications window appears. Select the Insert tab on the toolbar. Then, select the Module option in the scrolling list.

Guide on how to split Excel Sheets into separate Excel files

Step 3

At this point, the Module dialog box appears. Copy the following code into the dialog box.

Sub Splitbook()
'Updateby20140612
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

Then click on the Run icon on the toolbar to run this code.

Guide on how to split Excel Sheets into separate Excel files

Step 4

After running the code, the result you will get is that each sheet is split into a separate Excel file while still preserving the data.

Guide on how to split Excel Sheets into separate Excel files

When you open the separate Excel files for each sheet, you will see a notification as shown below. You just need to click Yes to be able to edit the data normally.

Guide on how to split Excel Sheets into separate Excel files

So, this article has guided you on how to split the sheet into separate Excel files. Hopefully, this article will be useful to you in your work. Wish you success!