A Workbook may have several worksheets, which may have a different dataset. Thankfully, Excel allows users to split each worksheet into a separate Excel file. The process of splitting the Workbook is simple and efficient. Secondly, splitting the Excel Workbook allows users to work on different worksheets simultaneously. In this post, we shall discuss common ways and workarounds that can be used to Split workbooks in Excel.
Using Move or Copy Tool to Split Excel sheets
Below are the steps to follow while using this method:
1. Open the Excel application.
2. Open the Excel workbook you wish to split its worksheets.
3. Locate the Worksheet you need to split and Right-click on it.
4. Click on the Move or Copy option from the Right-click menu.
5. A Move or Copy dialogue box will open. From the dialogue box, click the To Book drop-down button. From the menu, choose where you want to save the worksheet.
6. In the Before Sheet section, choose where you want the sheet to appear in the Workbook.
7. If you need to have a copy of the file, you can check the Create a copy checkbox. Finally, click the OK button.
Using VBA code to split the Workbook
Steps to follow:
1. Open the Excel application.
2. Open the Excel workbook you wish to split its worksheets.
3. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button. Alternatively, press the Alt + F11 keys on your keyboard to open the Visual Basic feature.
4. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.
5. Type the following code in the empty module.
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 & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
6. Press the F5 key to run the above code. The worksheet will be separated into different files.
Using Kutools to Split a workbook
Kutools is an add-ins feature used to modify and perform other calculations on an Excel document. Below are steps to follow while using this tool to Split a Workbook:
1. Download and Install the Kutools tool.
2. Open the Excel application. Then, open the Excel workbook you wish to split its worksheets.
3. On the Excel Ribbon, click on the Kutools Plus tab. Locate the Workbooks & Sheets section, and click on the Split Workbook button.
4. Select the worksheets you wish to split in the Split Workbook dialogue box.
5. Then, check the Skip Hidden Worksheets and Skip blank Worksheets checkboxes.
6. Next, check the Save as type checkbox, and choose the saving option you want from the drop-down button.
7. Finally, hit the Split button. That is all you need to do when using this method to split worksheets.
NOTE:
You can also use the Copy and Paste feature to split Excel Sheets into separate files.