How to Split Excel Sheet into Multiple Files (3 Quick Methods)

In an Excel workbook, we may have several worksheets at times. Suppose, we only want to work with a particular worksheet from our workbook. So, we may need to split the Excel sheet into multiple separate files for the convenience of our work. In this tutorial, we will explain different methods to split Excel worksheets of a workbook into multiple files. In this article, we will show you 3 quick methods to separate the Excel sheet into multiple files. First, we will show you 2 manual ways to split sheets. Then, we will split Excel sheets using VBA code.

1. Applying Manual Approach to Split Excel Sheet into Multiple Files

1.1 Using Copy and Paste Options

In the following dataset, we have sales data of 5 people for 4 years in 4 different worksheets. Suppose, we want to split each Excel sheet of the workbook into separate files. We will use simple copy and paste options to do this.

So, let’s see the steps to perform this action.

STEPS:

• To begin with, click on the sheet 2019.
• In addition, click on the triangular select all button on the top left corner or press Ctrl + A to select all the worksheets.
• Furthermore, click on the Copy from the Home tab or press Ctrl + C to copy the entire selected sheet.

• Next, open a new worksheet.
• Then, select cell A1 or the entire worksheet by clicking on the triangular select all button.
• After that, press Ctrl + V to paste the copied data.
• Lastly, we get the result like the following image. So, from the above actions, we get the first sheet of our Excel workbook separated into an individual Excel file.

NOTE: To split the other 3 Excel files we have to use copy and paste again and again. So, this method is not only inefficient but also time-consuming.

Read More: How to Split Excel Sheet into Multiple Sheets Based on Column Value

1.2 Applying Move or Copy Feature

In this method, we will split the Excel sheet into multiple files using the ‘Move or Copy’ feature. To illustrate this method we will continue with our previous dataset. Follow the below steps to execute this method.

STEPS:

• First, right-click on the sheet named 2019.
• Next, select the option ‘Move or Copy’.

• The above command will open a new pop-up window named ‘Move or Copy’.
• Then, select the option (new book) from the To book drop-down list.
• After that, check the option Create a Copy and click on OK.

• Finally, a copy of the worksheet 2019 will open in a new worksheet. As a result, we get the sheets separated from the original workbook.

NOTE:

Like the previous method, this method is also tedious and time-consuming. Because with this method we can move or copy only a single worksheet at a time. If we want to split multiple worksheets with a single move this method will not work.

Read More: Excel VBA: Split Sheet into Multiple Sheets Based on Rows

2. Running VBA Macro to Split Excel Sheet into Multiple Files

In this section of the tutorial, we will provide you with some VBA codes to split the Excel sheet into multiple files. We can quickly split all worksheets in a workbook into separate files using VBA code and save them in any predefined folder. To use VBA code to split Excel sheets we have to ensure two things:

• First, make a folder in which we will store our output files.
• Then, save the main Excel file in the newly created folder.

2.1 Splitting Into Multiple Excel Files

This method will show you how to split Excel sheets into multiple Excel files. Go through the below steps to perform this method.

STEPS:

• Firstly, save our main Excel file ‘Split Excel Sheet.xlsx’ in the folder where we want to store the output results.

• Secondly, open the original workbook.

• Thirdly, go to the Developer Select the option ‘Visual Basic’ from the ribbon.

• This will open the VBA We can also press Alt + F11 to open that editor.
• Next, right-click on any object from the section ‘Microsoft Excel Objects’.
• Then, select the option Insert and click on Module.

• Furthermore, the above command will open a blank VBA code window.
• After that, type the following code in the blank code window:

Sub Split_Sheet_into_ExcelFiles()
Dim FilePath As String
FilePath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each Sheet In ThisWorkbook.Sheets
Sheet.Copy
Application.ActiveWorkbook.SaveAs Filename:=FilePath & "\" & Sheet.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

• Now, click on the Run button or press the F5 key to run the code.

• Lastly, the above actions split all the sheets of our original workbook into individual Excel files. We get the newly separated Excel files in the same folder where we placed our original workbook.

2.2  Splitting Into Multiple PDF Files

In this method, we will split Excel worksheets into multiple PDF files. We will use the same dataset for this method that we used in the previous method. This is almost similar to the previous method. The only difference is that the output of this method will be in PDF format whereas in the previous method, the output was in Excel format.

Let’s see the steps to do this.

STEPS:

• In the beginning, go to the Developer tab.
• Next, from the ribbon select the option ‘Visual Basic’.

• The VBA editor will open as a result of this. We can also open the editor by pressing Alt + F11.
• Then, right-click any object in the ‘Microsoft Excel Objects‘ area.
• Next, select Insert and click on the option Module.

• Now, a blank VBA code window will appear.
• Furthermore, type the following code in that code window:

Sub Split_Sheet_into_PDF()
Dim FilePath As String
FilePath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each Sheet In ThisWorkbook.Sheets
Sheet.Copy
Application.ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, Filename:=FilePath & "\" & Sheet.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

• To run the code click on the Run button or press the F5 key.

• In the end, we get results like the following image. We can see the Excel sheets separated in PDF format in the folder where we place our original file.

Read More: How to Split Sheet into Multiple Sheets Based on Rows in Excel

3. Using VBA to Split Excel Sheets Containing Specific Word or Phrase into Multiple Files

To illustrate this method we are continuing with our previous dataset for this method also. We always do not have the need to split all the sheets from an Excel workbook. For instance, we may have the need to split worksheets into multiple files from our original workbook that contains specific words or phrases. From the following dataset, we will separate the sheets that only contain text 22 in their name. To do this we will apply a simple VBA code.

So, let’s see the steps to do this method.

STEPS:

• To begin with, go to the Developer and select the option ‘Visual Basic’.

• As a result, the VBA editor will appear. We can also access the editor by pressing Alt + F11.
• In addition, in the ‘Microsoft Excel Objects‘ box, right-click on any object.
• Furthermore, select Insert and click on the option Module.

• So, a new blank VBA code window will appear.
• Then, insert the following code in that code window:

Sub Split_Sheet_Specific_Word()
Dim FilePath As String
Dim Find As String
Find = "22"
FilePath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each Sheet In ThisWorkbook.Sheets
If InStr(1, Sheet.Name, Find, vbBinaryCompare) <> 0 Then
Sheet.Copy
Application.ActiveWorkbook.SaveAs _
Filename:=FilePath & "\" & Sheet.Name & ".xlsx"
Application.ActiveWorkbook.Close False
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

• After that, click on the Run button or press the F5 key to run the code.

• Finally, we can see the result in the following image. We get the sheet that has 22 in its name in the folder containing our original file.

Read More: How to Split Excel Sheet into Multiple Worksheets

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

In conclusion, this tutorial shows different methods to split sheets into Excel files. To put your skills into practice, use the sample worksheet provided in this article. Please feel free to comment in the box below if you have any questions.

Related Articles

• How to Split Screen in Excel
• [Fix:] Excel View Side by Side Not Working
• How to Enable Side-by-Side View with Vertical Alignments in Excel
• How to View Excel Sheets in Separate Windows