How to Separate Excel Tabs Into Separate Files

Spreadsheet Planet

Sometimes, you might need to divide the Excel sheets in your workbook into separate Excel or PDF files for various reasons.

This tutorial shows five techniques for separating Excel sheets into separate files.

Table of Contents

Method 1: Using Cut and Paste to Separate Excel Sheets into Separate Excel Files

If your Excel file has only a few sheets, you can use “Copy” and “Paste” to divide it into separate workbooks.

Suppose you have the following workbook with four worksheets.

We want to use “Copy” and “Paste” to split the workbook into separate files.

We use the below steps:

  1. Create a new folder on your computer where you can save the separated files. Once you’ve created the folder, save the main workbook in it.
  2. On the first worksheet that you want to separate, click the “Select All” button in the upper-left corner to select all the data on the worksheet and press “Ctrl + X” to cut it.

  1. Create a new workbook, select a cell in the workbook, and press “Ctrl + V” or press “Enter” to paste the data.
  2. Save the new workbook with an appropriate name in the folder you created earlier.
  3. Repeat steps 2 to 4 for the other sheets you want to separate into individual files.

After completing the steps above for each target sheet, separate Excel files for each sheet in the original file will be created, as shown below:

Note: Separating a large file with multiple worksheets into individual files using this copy-and-paste technique can be tedious and cumbersome. To simplify this process, you can try the move or copy method covered next.

Method 2: Use the Move or Copy Feature to Separate Excel Sheets into Separate Excel Files

If you have a workbook with only a few sheets, you can utilize the “Move or Copy” feature to divide them into separate files.

Let’s consider the following workbook, which has four worksheets:

We want to use the “Move or Copy” feature to split the worksheets into separate workbooks.

We use the following steps:

  1. Create a new folder on your computer where you want to save the separated files and save the main workbook into this folder. The folder will help keep everything organized.
  2. Go to the first sheet you want to separate. Right-click on the sheet tab at the bottom of the Excel window and select “Move or Copy.”

Note: To select multiple sheets not next to each other, press and hold the “Ctrl” key, and then click on each sheet tab one by one.

If you want to select multiple sheets next to each other, hold down the “Shift” key and click on the sheet tabs in sequence.

  1. In the “Move or Copy” dialog box, you will see a “To book” dropdown. Select “(new book)” from the list.

  1. Leave the “Create a copy” checkbox unchecked to move the sheet instead of copying it.

  1. Click the “OK” button to create a new Excel file with only the selected sheet.
  2. Save the new file with an appropriate name in the folder you created earlier.
  3. Repeat steps 2 to 7 for each sheet you want to separate into individual files.

Once you have followed these steps for all the desired sheets, you will have separate Excel files for each sheet in the original file, as shown below:

Note: The current method can be tedious and error-prone if you’re dealing with a workbook with numerous sheets. In such cases, it’s better to use the following alternative technique.

Method 3: Use Excel VBA Code to Separate Excel Sheets into Separate Excel Files

If you have a workbook with numerous sheets, you can utilize Excel VBA code to divide them into separate files.

Let’s assume we have the following workbook with ten worksheets.

We want to create Excel VBA code and apply it to split the worksheets into individual files.

We use the steps below:

  1. Create a new folder on your computer where you want to save the separated files and save the main workbook into this folder.
  2. Open any worksheet in the main workbook and press “Alt + F11” to open the “Visual Basic Editor.” Alternatively, open the “Developer” tab and click the “Visual Basic” button on the “Code” group:

  1. Open the “Insert” menu and choose the “Module” option.

  1. Copy and paste the following sub-procedure into the module:
'Code developed by Steve Scott from https://spreadsheetplanet.com Sub SeparateSheets() Dim FilePath As String Dim wsh As Worksheet FilePath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Each wsh In ThisWorkbook.Sheets wsh.Copy Application.ActiveWorkbook.SaveAs Filename:=FilePath & "\" & wsh.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub

The code should appear in the module as follows:

  1. Save the Excel file as an “Excel Macro-Enabled Workbook.”
  2. Press “Alt + F11” to switch to the active worksheet.
  3. Press “Alt + F8” to open the “Macro” dialog box, select the “SeparateSheets” macro on the “Macro name” list box and click the “Run” button.

The code runs and divides the worksheets in the main workbook into separate files, as seen in the folder you created in Step 1:

Explanation of the Excel VBA Code

This VBA code separates each worksheet in the current workbook into individual Excel files using the worksheet name as the filename and saving them in the same folder as the original workbook.

Here’s a breakdown of the code:

  1. The code begins with the declaration of two variables:
  2. FilePath = Application.ActiveWorkbook.Path retrieves the path of the active workbook and assigns it to the `FilePath` variable.
  3. The lines Application.ScreenUpdating = False and Application.DisplayAlerts = False are used to improve the performance of the code by turning off screen updating and suppressing any alert messages.
  4. The code then enters a loop with the For Each statement, iterating through each worksheet in the current workbook using the ThisWorkbook.Sheets collection.
  5. Inside the loop, wsh.Copy creates a copy of the current worksheet.
  6. Application.ActiveWorkbook.SaveAs saves the copied worksheet as a separate Excel file. The Filename parameter specifies the file path, which combines the original FilePath, the worksheet name (wsh.Name), and the extension “.xlsx.”
  7. After saving the worksheet as a separate file, Application.ActiveWorkbook.Close False closes the copied workbook without saving any changes.
  8. The loop continues to the following worksheet until all worksheets are processed.
  9. Application.DisplayAlerts = True restores the display of alert messages.
  10. Application.ScreenUpdating = True enables screen updating again.

Method 4: Use Excel VBA Code to Separate Excel Sheets With Names Having Particular Phrases into Separate Excel Files

You may want to separate only those worksheets with names having particular phrases into separate workbooks. You can use Excel VBA code to perform the task.

Let’s say you have a workbook that contains sales data for multiple quarters, and each worksheet’s name is labeled with the corresponding quarter number, as depicted below.:

If, for example, you want to separate all the worksheets for “Quarter 1” and save them as individual Excel files, use the steps below:

  1. Create a new folder on your computer where you want to save the separated files and save the main workbook into this folder.
  2. Create a new module in “Visual Basic Editor,” as described in Method #3.
  3. Copy and paste the following code into the module:
'Code developed by Steve Scott from https://spreadsheetplanet.com Sub SeparateSheetWithPhrase() Dim FilePath As String Dim TexttoFind As String Dim wsh As Worksheet TexttoFind = "Quarter 1" FilePath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Each wsh In ThisWorkbook.Sheets If InStr(1, wsh.Name, TexttoFind, vbBinaryCompare) <> 0 Then wsh.Copy Application.ActiveWorkbook.SaveAs Filename:=FilePath & "\" & wsh.Name & ".xlsx" Application.ActiveWorkbook.Close False End If Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub

The code should appear in the module as shown below:

  1. Save the Excel file as an “Excel Macro-Enabled Workbook.”
  2. Execute the code as explained in Method #3.

The code runs and separates the worksheets that have the phrase “Quarter 1” in their names into individual Excel files, as shown below:

Explanation of the Code

The Excel VBA macro in this method searches for worksheets in the active workbook that contain the phrase “Quarter 1” in their names. For each matching worksheet found, it creates a copy of that worksheet as a separate Excel file, saving it in the same directory as the original workbook.

The breakdown of the code is the same as explained in Method #3, except for the following additions:

  1. An additional string variable, `TexttoFind,` is defined and stores the phrase to be searched in the worksheet names.
  2. The “InStr” function searches for the occurrence of the phrase stored in the `TexttoFind` variable within `wsh.Name` (worksheet name). If the phrase is found, “InStr” returns a value greater than 0, and the condition “InStr(1, wsh.Name, TexttoFind, vbBinaryCompare) <> 0” evaluates to “True” and the worksheet is saved as a separate Excel file.

Method 5: Use Excel VBA Code to Separate Excel Sheets into Separate PDF Files

If you need to divide your Excel sheets within a workbook and save them as separate PDF files, you can use Excel VBA code to accomplish the task.

Let’s assume we have the following workbook with ten worksheets.

We can divide the sheets in the workbook into separate PDF files using the following steps:

  1. Create a new folder on your computer where you want to save the separated PDF files and save the main workbook into this folder.
  2. Create a new module in “Visual Basic Editor,” as explained in Method #3.
  3. Copy and paste the following code into the module:
'Code developed by Steve Scott from https://spreadsheetplanet.com Sub SeparateWorksheetsToPdfs() Dim FilePath As String Dim wsh As Worksheet FilePath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Each wsh In ThisWorkbook.Sheets wsh.Copy Application.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FilePath & "\" & wsh.Name & ".pdf" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub

The code should appear in the module as depicted below:

  1. Save the Excel file as an “Excel Macro-Enabled Workbook.”
  2. Execute the code as explained in Method #3.

The code runs and separates the worksheets into individual Excel PDF files, as shown below:

Explanation of the Code

The code saves each worksheet in the active workbook as a separate PDF file in the same directory as the workbook itself. It temporarily turns off screen updating and alerts, iterates through each worksheet, creates a copy, exports it as a PDF, and then closes the workbook. Finally, it restores the settings for alerts and screen updating.

The breakdown of the code is as explained in Method #3, except for the following additional statement:

Some Usecases Where you May Want to Split Excel Tabs into Separate Files

This tutorial showed five techniques for separating Excel sheets into separate files. We hope you found the tutorial helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.