How to extract Excel Sheet from Workbook: 4 easy ways

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

How to split Excel Workbook

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

How to extract Excel Sheet from Workbook
How to extract Excel Sheet from 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

How to combine the separate workbooks into one main Workbook

2. Select worksheets that you will put in the master workbook

How to combine the separate workbooks into one main 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 combine the separate workbooks into one main Workbook

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

Leave a Reply