In this tutorial, I will show you how to extract, split or move Excel sheets from workbook.
Use move or copy to Extract Excel Sheet from the workbook
This is the first step. Follow the simple steps below;
1. Use your mouse to Right-click the name tab of the worksheet.
2. Select Move / Copy
3. Select the ‘Move selected sheet to Book’ choice from the menu that appears.
4. Click on New book
5. Click OK
The Excel worksheet will be extracted to a new workbook from the previous workbook.
How to extract Excel sheet from Workbook while still retaining the main copy
You can also make the worksheet appear in both workbooks. Follow the steps below to do this;
1. Right-click with your mouse on the name tab of the worksheet
2. Choose Move and Copy
3. Select the ‘Create a copy’ choice that appears in the menu
4. Press OK
5. Press on File, then Save.
To save the new Excel workbook containing the worksheet you have just extracted, you can also use the shortcut Ctrl+ S.
Use KUTOOLS to split the Workbook (Split multiple sheets at once)
Kutools is an Excel plugin by Extend Office, which comes with multiple functionalities to make work easier. You can also use KUTOOLS to save and Export each Worksheet separately easily. With Kutools, you need to perform a few clicks to extract an Excel sheet from the workbook. Follow these simple
steps.
1. Download and install Kutools for Excel
2. Open Kutools Plus
3. Press Workbook
4. Click on Split Workbook
In the Split Workbook section, you will see that all the names of the worksheet have been checked. This is a default setting. Just uncheck the boxes to split the worksheets.
5. Select the Split button
6. Select the file to which you want to add new workbooks to
7. Press Split
This step allows the checked worksheets in the Split Workbooks to be exported and saved separately.
How to combine the separate workbooks into one main Workbook
In order to combine the separate workbooks into one common workbook as before you separated them, you can do as follows;
1. Click to open the workbooks that you want to combine into one main workbook
2. Select worksheets that you will put in the master workbook
3. Use the mouse to right-click the tabs
4. Select Move and Copy from the menu that appears
5. Select ‘the excel sheet you wish to copy/move to under to book’, then select move or copy
6. Press OK
How to Split Excel Worksheets using VBA
You can use VBA code to split all Worksheets to new workbooks in Excel. If you are looking for a way to split your worksheets to new workbooks easily then you need to consider using VBA code: Here are the steps.
1. Press Alt+F11 to open the Visual Basic Editor in Excel.
2. Insert a new module by clicking Insert > Module.
3. Copy and paste the code below into the module.
Sub SplitWorksheetsToWorkbooks()
Dim ws As Worksheet
Dim newWorkbook As Workbook
Dim path As String
' Get the path of the current workbook
path = ThisWorkbook.Path & "\"
' Disable screen updating and alerts
Application.ScreenUpdating = False
Application.DisplayAlerts = False
' Loop through each worksheet in the workbook
For Each ws In ThisWorkbook.Worksheets
' Create a new workbook
Set newWorkbook = Workbooks.Add
' Copy the current worksheet to the new workbook
ws.Copy Before:=newWorkbook.Sheets(1)
' Save the new workbook with the name of the worksheet
newWorkbook.SaveAs path & ws.Name & ".xlsx"
' Close the new workbook
newWorkbook.Close SaveChanges:=False
Next ws
' Enable screen updating and alerts
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "Splitting worksheets to workbooks completed!"
End Sub
4. Close the Visual Basic Editor.
5. Press Alt+F8 to open the Macros dialog box.
6. Select the SplitWorksheetsToWorkbooks macro from the list and click Run