Split Each Excel Sheet Into Separate Files (Manual + VBA)

If you have an Excel workbook with many worksheets, there is no easy way to split each of these sheets into separate Excel files and save separately.

This could be needed when you have sheets for different months or regions or products/clients, and you want a separate workbook for each sheet (as an Excel file or as a PDF).

There is a manual way to do this, which works fine for a few sheets. But with a lot of sheets, it’s slow and error-prone.

In this tutorial, I will show you the manual way, and then give you a simple VBA code that splits all the worksheets into their own separate files (or PDFs) in a few seconds.

Split Sheets Into Separate Files Manually (Without VBA)

If you only have a handful of sheets, you don’t need any code. You can copy each sheet into a new workbook and save it.

Below I have a workbook with six sheets of bakery sales, one for each month, and I want each sheet saved as its own Excel file.

Here are the steps to save one sheet as a separate Excel file:

1. Right-click on the sheet tab that you want to save as a separate file (2025 Jan in this example), and click on Move or Copy.

2. In the Move or Copy dialog box, click the ‘To’ drop-down and select (new book).

3. Check the ‘Create a copy’ option and click OK.

Excel opens a new workbook that has only this one sheet in it.

Important: If you leave ‘Create a copy’ unchecked, Excel moves the sheet out of your main file instead of copying it.

4. Press the F12 key (Cmd + Shift + S on a Mac) to open the Save As dialog box, and go to the folder where you want to save the file.

5. Type the file name (such as 2025 Jan) in the File name field and click Save.

Now close this new workbook, go back to your main file, and repeat these steps for every other sheet.

If you select multiple sheet tabs (hold Ctrl, or Cmd on a Mac, and click them) and then use Move or Copy, all of them go into one new workbook together.

That’s handy, but you end up with one file that has all those sheets in it.

With six sheets, this is manageable. With 30 or 50 sheets, it gets tedious and it’s easy to skip or misname one, and that’s where the VBA code below helps.

If you only need to copy one sheet somewhere, here’s more on how to copy a worksheet in Excel. You can also copy a sheet to a new workbook using VBA.

Split Each Worksheet Into a Separate Excel File Using VBA

Below I have the same workbook with a worksheet for each month, and I want every sheet saved as its own Excel file in one go.

To split these sheets into separate Excel files, you can use the below VBA code:

'Code Created by Sumit Bansal from trumpexcel.com
Sub SplitEachWorksheet()
Dim FPath As String
Dim ws As Object
FPath = ThisWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
    If ws.Visible = xlSheetVisible Then
        ws.Copy
        Application.ActiveWorkbook.SaveAs Filename:=FPath & Application.PathSeparator & ws.Name & ".xlsx"
        Application.ActiveWorkbook.Close False
    End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

'Code Created by Sumit Bansal from trumpexcel.comSub SplitEachWorksheet()Dim FPath As StringDim ws As ObjectFPath = ThisWorkbook.PathApplication.ScreenUpdating = FalseApplication.DisplayAlerts = FalseFor Each ws In ThisWorkbook.Sheets    If ws.Visible = xlSheetVisible Then        ws.Copy        Application.ActiveWorkbook.SaveAs Filename:=FPath & Application.PathSeparator & ws.Name & ".xlsx"        Application.ActiveWorkbook.Close False    End IfNextApplication.DisplayAlerts = TrueApplication.ScreenUpdating = TrueEnd Sub

There are a few things you need to make sure before using the above VBA code:

1. Create a folder where you want to get all the resulting files.
2. Save the main Excel file (which has all the worksheets that you want as separate files) in this folder.

Once you have this done, then you can put the above VBA code in the file and run the code.

The above code is written in a way that it picks up the location of the folder using the path of the file (in which the code is run).

This is why it’s important to save the file in the folder first and then use this code.

How does the VBA code work – The above code uses a simple For Next loop that goes through each worksheet and creates a copy of the worksheet in a new Excel workbook.

It then saves this workbook in the specified folder (which is the same that has the main file with all the sheets) and closes it.

The code only splits visible sheets. Excel can’t copy a hidden sheet into a new workbook on its own, so the code skips hidden sheets instead of stopping with an error.

If you want a hidden sheet split too, unhide the sheets before you run the code.

It also uses Application.PathSeparator instead of a hard-coded backslash, so the file path gets the right separator whether you’re on Windows or a Mac.

Important: If the folder already has a file with the same name as a sheet, the codes in this tutorial overwrite it without asking.

Below are the steps to place this VBA code in the Excel workbook (these will be same for all the other methods shown in this tutorial).

Where to put this code?

Below are the steps to place the code in the Visual Basic Editor where it can be executed:

1. Click the Developer tab.

2. In the Code group, click on the Visual Basic option. This will open the VB Editor. [You can also use the keyboard shortcut – ALT + F11]

3. In the VB Editor, right-click on any of the objects of the workbook you’re working on.

4. Hover the cursor over the Insert option and click on Module. This will insert a new module.

This also opens the code window for the module. If it doesn’t, double-click on the Module object to open it.

5. Copy the VBA code provided above and paste it in the module code window.

6. Select any line in the code and click on the green play button in the toolbar to run the VBA macro code.

The above steps would instantly split the worksheets into separate Excel files and save these.

It takes only a second if you have a few worksheets. In case you have a lot, it may take some time.

The name of each saved file is the same as that of the sheet name it had in the main file.

Since you have placed a VBA code in the Excel workbook, you need to save this with a .XLSM format (which is the macro-enabled format).

This will ensure the macro is saved and works when you open this file next.

Note that I have used the lines Application.ScreenUpdating = False and Application.DisplayAlerts = False in the code so that everything happens in the backend.

This way, you don’t see things happening on your screen.

Once the code runs and splits the sheets and saves these, we turn these back to TRUE.

As a best practice, it’s recommended to create a backup copy of the main file (which has the sheets that you want to split).

This will ensure you don’t lose your data in case anything goes wrong or if Excel decides to become slow or crash.

If a sheet has formulas that refer to other sheets, those formulas become external links to your main file in the new files (the manual method does this too).

If you want the new files to hold plain values instead, add this line right after the ws.Copy line in the code:

ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

This converts every formula to its value in the copied sheet, so do this only when the split files don’t need live formulas.

Also, use it only when the workbook has no chart sheets, as a chart sheet has no cells and stops the code with an error.

VBA doesn’t run in Excel for the web, so you need the desktop version of Excel (Windows or Mac) for all the codes in this tutorial.

Also read: Inserting Excel Tables Into Word

Split Each Worksheet and Save as Separate PDFs

Below I have the same workbook with one sheet for each month, and this time I want each sheet saved as its own PDF file.

In case you want to split the worksheets and save these as PDF files instead of the Excel files, you can use the below code:

'Code Created by Sumit Bansal from trumpexcel.com
Sub SplitEachWorksheetToPDF()
Dim FPath As String
Dim ws As Object
FPath = ThisWorkbook.Path
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Sheets
    If ws.Visible = xlSheetVisible Then
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FPath & Application.PathSeparator & ws.Name & ".pdf"
    End If
Next
Application.ScreenUpdating = True
End Sub

'Code Created by Sumit Bansal from trumpexcel.comSub SplitEachWorksheetToPDF()Dim FPath As StringDim ws As ObjectFPath = ThisWorkbook.PathApplication.ScreenUpdating = FalseFor Each ws In ThisWorkbook.Sheets    If ws.Visible = xlSheetVisible Then        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FPath & Application.PathSeparator & ws.Name & ".pdf"    End IfNextApplication.ScreenUpdating = TrueEnd Sub

Things you need to make sure before using this code:

1. Create a folder where you want to get all the resulting files.
2. Save the main Excel file (which has all the worksheets that you want as separate files) in this folder.

The above code splits each sheet in the Excel file and saves it as a PDF in the same folder where you have saved the main Excel file.

Unlike the previous code, this one doesn’t need to copy each sheet into a new workbook first. It exports every visible sheet straight to a PDF named after the sheet.

Each PDF uses the page setup of its sheet (print area, orientation, and margins).

So set the print area and layout first if you want the PDFs to look a certain way.

Pro Tip: Each code in this tutorial has its own name, so you can paste all of them into the same module and run whichever one you need.

Also read: Insert Pdf Into Excel

Split Only Sheets That Contain Specific Text Into Separate Files

In case you have a lot of sheets in a workbook and you only want to split those sheets that have a specific text in their name, you can do that as well.

For example, suppose you have an Excel file where you have data for multiple years and each sheet in the file has the year number as the prefix. Something as shown below:

Now, let’s say you want to split all the sheets for 2026 and save these as separate Excel files.

To do this, you need to somehow check the name of each worksheet, and only those sheets that have the number 2026 should be split and saved, and the rest should be left untouched.

This can be done using the following VBA macro code:

'Code Created by Sumit Bansal from trumpexcel.com
Sub SplitSheetsContainingText()
Dim FPath As String
Dim TexttoFind As String
Dim ws As Object
TexttoFind = "2026"
FPath = ThisWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each ws In ThisWorkbook.Sheets
    If ws.Visible = xlSheetVisible And InStr(1, ws.Name, TexttoFind, vbBinaryCompare) <> 0 Then
        ws.Copy
        Application.ActiveWorkbook.SaveAs Filename:=FPath & Application.PathSeparator & ws.Name & ".xlsx"
        Application.ActiveWorkbook.Close False
    End If
Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

'Code Created by Sumit Bansal from trumpexcel.comSub SplitSheetsContainingText()Dim FPath As StringDim TexttoFind As StringDim ws As ObjectTexttoFind = "2026"FPath = ThisWorkbook.PathApplication.ScreenUpdating = FalseApplication.DisplayAlerts = FalseFor Each ws In ThisWorkbook.Sheets    If ws.Visible = xlSheetVisible And InStr(1, ws.Name, TexttoFind, vbBinaryCompare) <> 0 Then        ws.Copy        Application.ActiveWorkbook.SaveAs Filename:=FPath & Application.PathSeparator & ws.Name & ".xlsx"        Application.ActiveWorkbook.Close False    End IfNextApplication.DisplayAlerts = TrueApplication.ScreenUpdating = TrueEnd Sub

In the above code, I have used a variable TexttoFind, which has been assigned to “2026” in the beginning.

The VBA code then uses the For Next loop in VBA to go through each worksheet and check the name of each worksheet using the INSTR function.

This function checks whether the worksheet name has the text 2026 in it or not. If it does, it will return a position number where it finds this text (which is 2026 in this case).

And if it doesn’t find the text we are looking for, it returns 0.

This is used with the IF Then condition. So if a sheet name has the text string 2026 in it, it will be split and saved as a separate file.

And if it doesn’t have this text string, the IF condition would not be met and nothing would happen. In this example, you get three files: 2026 Jan, 2026 Feb, and 2026 Mar.

Pro Tip: vbBinaryCompare makes the match case-sensitive. If you’re matching a word like “Sales”, change it to vbTextCompare so “sales” and “SALES” also match.

So these are the ways you can split each Excel sheet into separate files, either manually or with a simple VBA code, as Excel files or as PDFs.

I hope you found this Excel tutorial useful.

You may also like the following Excel tutorials:

• How to Compare Two Excel Sheets
• How to Reduce Excel File Size
• Combine Data from Multiple Workbooks in Excel
• How to Recover Unsaved Excel Files [All Options + Precautions]
• How to Automatically Open Specific Excel File on Startup
• How to Split Multiple Lines in a Cell into a Separate Cells / Columns
• Excel VBA Split Function
• How to Import XML File into Excel | Convert XML to Excel
• How to Split Screen in Excel (Compare Side-by-Side)
• Using Workbook Object in Excel VBA (Open, Close, Save, Set)
• How to Print Multiple Sheets (or All Sheets) in Excel in One Go