How to Save Multiple Tabs in Excel as Separate Files in 2024

In this article, we will learn how to save a sheet in Excel as a separate file. Simply follow the steps below.

How to Save Each Sheet as Separate Excel File

Follow the steps below to save Excel tabs as separate files: 

1. Open Your Workbook

Open the Excel workbook that contains the worksheets you want to save as separate files.

2. Select the Worksheet

Click on the tab of the worksheet you want to save as a separate file. For example, we have Sheet1.

3. Save As

Go to the File menu in the top left corner of Excel. 

Click on Save As. 

Choose the location where you want to save the file. In the Save as type dropdown menu, ensure it is set to Excel Workbook (*.xlsx) or another desired format.

4. Save

Click Save. The current worksheet will now be saved as a new Excel file.

5. Repeat for Other Worksheets

Repeat steps 2 through 5 for each worksheet in the workbook that you want to save as a separate file.

How to Save Excel Sheets as Separate Files

If you have many tabs and want to automate this task, you can use a VBA macro. Here’s how to do it:

1. Open Your Workbook

Ensure your workbook is open in Excel.

2. Save Your Workbook

Make sure your workbook is saved. This is important because the macro references the workbook’s path to save each worksheet. If the workbook isn’t saved, Excel won’t have a default path, and the macro may fail.

Press CTRL + S and a dialog box should appear where you can save your file.

3. Press ALT + F11

This opens the Visual Basic for Applications editor.

4. Insert a New Module

In the VBA editor, right-click on any of the objects in the project explorer. Choose Insert > Module. This adds a new module to your project.

5. Enter the Macro Code

Copy and paste the following VBA code into the module:

Sub SaveWorksheetsAsSeparateFiles()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Copy
        Application.ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ws.Name & ".xlsx"
        Application.ActiveWorkbook.Close False
    Next ws
End Sub

6. Run the Macro

Close the VBA editor and return to Excel. Press ALT + F8, select “SaveWorksheetsAsSeparateFiles”, and click “Run”.

Each sheet should now be saved as separate files in the same folder as the workbook itself.

We hope that you now have a better understanding of how to save multiple tabs in Excel as separate files. If you enjoyed this article, you might also like our articles on how to highlight selected cells in Excel and how to save an Excel chart as an image.