How to Save Each Excel Sheet as a Separate File

How to save each Excel sheet as a separate file

To save one Excel sheet as its own file, right-click its tab, choose Move or Copy, select (new book), tick Create a copy, click OK, and save the new workbook. For a workbook with a dozen tabs, a short macro does every sheet in one pass.

Whichever route you take, check one thing afterwards. Formulas that pointed at another sheet in the original workbook now point back at the original file, and that surprises almost everyone.

TL;DR: Key takeaways

• Move or Copy > (new book) saves one sheet as its own workbook. Tick Create a copy or the sheet leaves the original.
• For many sheets, the macro below writes each visible sheet to its own .xlsx.
• Formulas referring to other sheets become links back to the original file.
• If the destination only needs data, export each sheet to CSV instead.

One sheet: Move or Copy

Microsoft's steps for a single sheet:

1. Right-click the sheet tab.
2. Choose Move or Copy.
3. In To book, select (new book).
4. Tick Create a copy, then click OK.
5. Save the new workbook with Ctrl + S.

Step 4 matters. Without the checkbox, Excel moves the sheet out of your original workbook rather than copying it.

This is fine for two or three sheets. At ten, it becomes an afternoon.

Every sheet at once: a short macro

This macro copies each visible sheet into its own workbook, names the file after the sheet, and saves it next to the original.

Sub SaveEachSheetAsWorkbook()
    Dim src As Workbook, ws As Worksheet, folder As String
    Set src = ActiveWorkbook
    If src.Path = "" Then
        MsgBox "Save this workbook first so the new files have a folder."
        Exit Sub
    End If
    folder = src.Path & Application.PathSeparator
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each ws In src.Worksheets
        If ws.Visible = xlSheetVisible Then
            ws.Copy
            ActiveWorkbook.SaveAs Filename:=folder & ws.Name & ".xlsx", _
                FileFormat:=xlOpenXMLWorkbook
            ActiveWorkbook.Close SaveChanges:=False
        End If
    Next ws
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

To use it, open the VBA editor with Alt + F11, choose Insert > Module, paste the code, then run it with Alt + F8. Save the source workbook first, since the new files go in the same folder.

Three behaviours to know before running it. It skips hidden sheets. It overwrites any existing file with the same name without asking, because alerts are switched off. And because it saves as .xlsx, no VBA travels into the new files.

Check your formulas afterwards

This is the step every guide leaves out.

When a sheet is copied into a new workbook, any formula that referred to a different sheet in the original cannot find that sheet any more. Excel keeps the formula working by turning it into an external link to the original file. A Microsoft Q&A answer describes it directly: the formulas are converted into external links "causing them to still point to the original workbook."

In practice, each new file opens with a security bar about updating links, and its numbers depend on a file the recipient probably does not have. Microsoft's own guidance warns that moving a sheet to another workbook "might cause errors or produce unintended results" in formulas and charts.

If the files are going to other people, the usual fix is to convert formulas to values in each copy before sending it. The numbers stay, and the links disappear.

When CSV is the better answer

If whoever receives the files only needs the data, for a database import, a script, or a system that rejects Excel files, save each sheet as CSV instead. CSV carries values only, so the formula-link problem cannot occur.

Our free Export Each Sheet to CSV macro does that for every tab in one run, and exporting sheets to CSV covers the details.

Frequently asked questions

How do I save one Excel sheet as a separate file?

Right-click the sheet tab, choose Move or Copy, select (new book) in the To book list, tick Create a copy, and click OK. Excel opens a new workbook containing just that sheet. Save it with Ctrl + S.

How do I split every sheet into its own file at once?

Use a macro that loops through the sheets, copies each one into a new workbook and saves it. The macro in this article saves every visible sheet as an .xlsx named after the sheet, in the same folder as the original.

Why do my split files ask to update links?

Because formulas that referred to other sheets were turned into external links pointing at the original workbook. Convert formulas to values in each new file before sharing it, and the links are removed.

Will the new files keep my formatting?

Yes. Copying a sheet into a new workbook keeps its formatting, column widths and formulas. Only CSV drops formatting, which is why CSV suits data transfers rather than files people will read.

Does this work on a Mac?

Yes. Move or Copy works the same way in Excel for Mac. The macro uses Excel's own path separator rather than a hard-coded backslash, so its file paths are valid on either platform, though Excel for Mac may ask permission to use the folder the first time.

What to do next

For a one-off, use Move or Copy. For a monthly job with many tabs, keep the macro in your Personal Macro Workbook so it is available in every workbook. Then convert formulas to values in the copies before they leave your hands. To go the other way and bring separate files back together, see merging two Excel workbooks into one.