How to Split Excel Sheets to Separate Files

When working with Excel, you may want to separate worksheets into different files. There are different methods to separate them. If you have only a couple of sheets in the workbook, you can use the "Move and Copy" command to separate them one by one. However, when you have many worksheets in the workbook, you may need to think of some easier ways by using the macro codes. Please see below for details:

Step 1: Create a new folder and move the workbook into the folder. The reason is that the separated files will show up in the folder;

Step 2: Pressing shortcut "Alt+F11" to open the Microsoft Visual Basic for Applications window;

Alternatively, please click the "Developer" tab from the ribbon and click "Visual Basic" to open the window.

Step 3: In the new window, click the "Insert" tab from the ribbon, and click "Module";

Step 4: Copy and paste the following codes in the Module window;

Sub Splitbook()
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 & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Step 5: Click the "Run Sub" button (or press the F5 key) to run the codes;

Step 6: Go back to the folder and you will see all separated files along with the original workbook in the folder.