VBA to Save Excel Tabs as Separate Files

VBA & macros · Updated November 20, 2026

Yes. A VBA macro can copy each visible worksheet tab into a new workbook and save it as a separate .xlsx file; the example below asks you to choose an output folder and creates a distinct filename for each sheet. Save a copy of your workbook before running it, because macros cannot be undone with Ctrl+Z.

What the macro does

In Excel, a worksheet tab is one sheet inside a workbook. The macro below goes through the visible worksheets in the workbook that contains the code, copies each one into a new, single-sheet workbook, and saves that workbook in a folder you choose. The source workbook is not split apart or deleted. The exported files use the standard .xlsx format, which does not store VBA code.

This is useful when you need to send departments their own worksheet, deliver separate reports, or archive tabs individually. It is not the same as saving the entire workbook repeatedly under different names: each output workbook contains one copied worksheet, rather than all the original tabs.

  • It processes visible worksheets only. Hidden and very hidden worksheets are skipped.
  • It does not export chart sheets, which are separate from worksheets.
  • It keeps the source workbook open and saves new workbooks in the selected folder.
  • If a filename already exists, it adds a number such as “(2)” instead of deliberately replacing that file.

VBA macro to export worksheets

The code is intended for Excel for Microsoft 365 on Windows. It uses the workbook containing the macro as its source, so put the code in the workbook whose tabs you want to export. The filename helper replaces characters Windows does not allow in filenames and makes a new name when a file already exists.

Option Explicit

Sub ExportVisibleSheetsToFiles()
    Dim sourceWb As Workbook
    Dim ws As Worksheet
    Dim newWb As Workbook
    Dim dlg As Object
    Dim folder As String
    Dim filePath As String
    Dim oldAlerts As Boolean
    Dim oldScreenUpdating As Boolean
    Dim savedCount As Long

    On Error GoTo Fail
    Set sourceWb = ThisWorkbook
    oldAlerts = Application.DisplayAlerts
    oldScreenUpdating = Application.ScreenUpdating

    ' Ask where to save the separate workbooks.
    Set dlg = Application.FileDialog(4)
    With dlg
        .Title = "Choose a folder for the exported worksheets"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Sub
        folder = .SelectedItems(1)
    End With

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For Each ws In sourceWb.Worksheets
        If ws.Visible = xlSheetVisible Then
            filePath = UniquePath(folder, SafeFileName(ws.Name), ".xlsx")
            ws.Copy
            Set newWb = ActiveWorkbook
            newWb.SaveAs Filename:=filePath, _
                FileFormat:=xlOpenXMLWorkbook
            newWb.Close SaveChanges:=False
            Set newWb = Nothing
            savedCount = savedCount + 1
        End If
    Next ws

    Application.DisplayAlerts = oldAlerts
    Application.ScreenUpdating = oldScreenUpdating
    MsgBox savedCount & " worksheet file(s) saved to:" & vbCrLf & folder
    Exit Sub

Fail:
    On Error Resume Next
    If Not newWb Is Nothing Then newWb.Close SaveChanges:=False
    Application.DisplayAlerts = oldAlerts
    Application.ScreenUpdating = oldScreenUpdating
    MsgBox "Export stopped: " & Err.Description, vbExclamation
End Sub

Private Function SafeFileName(ByVal sheetName As String) As String
    Dim badChars As Variant
    Dim ch As Variant

    badChars = Array("\", "/", ":", "*", "?", Chr$(34), "<", ">", "|")
    For Each ch In badChars
        sheetName = Replace(sheetName, CStr(ch), "_")
    Next ch

    sheetName = Trim$(sheetName)
    Do While Len(sheetName) > 0 And _
        (Right$(sheetName, 1) = "." Or Right$(sheetName, 1) = " ")
        sheetName = Left$(sheetName, Len(sheetName) - 1)
    Loop
    If Len(sheetName) = 0 Then sheetName = "Sheet"
    SafeFileName = sheetName
End Function

Private Function UniquePath(ByVal folder As String, _
    ByVal baseName As String, ByVal extension As String) As String
    Dim candidate As String
    Dim suffix As Long

    candidate = folder & Application.PathSeparator & baseName & extension
    suffix = 2
    Do While Len(Dir$(candidate)) > 0
        candidate = folder & Application.PathSeparator & baseName & _
            " (" & CStr(suffix) & ")" & extension
        suffix = suffix + 1
    Loop
    UniquePath = candidate
End Function

Install and run it in Excel

  1. First save a backup copy of the source workbook. If it contains macros, keep the working copy in .xlsm format.
  2. Open that workbook and choose Developer > Visual Basic. If the Developer tab is not shown, enable it through File > Options > Customize Ribbon, then select Developer.
  3. In the Visual Basic Editor, choose Insert > Module, paste all of the code into the module, and save the workbook.
  4. Return to Excel and run ExportVisibleSheetsToFiles from Developer > Macros. Choose the macro and click Run, then select the destination folder in the dialog.

On Windows, Alt+F11 opens the Visual Basic Editor and Alt+F8 opens the macro list. On a Mac, Option+F11 and Option+F8 are common equivalents; depending on the keyboard, you may also need to hold Fn. Excel for the web cannot run VBA macros, so use the desktop Excel app. Excel for Mac supports VBA, but dialogs and file paths can differ; the supplied folder-picker code is aimed at Windows, so Mac users should test it on a copy and adapt the folder-selection method if needed.

How names and existing files are handled

Worksheet names are convenient starting points for filenames, but not every character allowed in a sheet name is allowed in a Windows filename. The helper replaces characters such as slash, colon, question mark, and quotation mark with underscores. It also trims spaces and trailing periods. For example, a tab called “North: Sales” becomes “North_ Sales.xlsx.” The code does not change the worksheet tab name in the source workbook.

The UniquePath function checks whether the proposed file already exists. If “Summary.xlsx” is already in the selected folder, the next matching export is saved as “Summary (2).xlsx,” then “Summary (3).xlsx,” and so on. This avoids routine overwriting, but it is still sensible to choose an empty output folder when you want a clean batch. Very long folder paths can also cause saving problems on some Windows setups.

What happens to formulas, formatting, and links

Copying a sheet carries much of its worksheet content and layout into the new workbook: cell values, formulas, formatting, column widths, row heights, and many sheet-level objects such as charts placed on the sheet. Always inspect a representative output, because workbook-level features do not necessarily transfer as a self-contained package. A worksheet copy does not bring along the other tabs as independent sheets.

Formulas deserve particular attention. A formula that refers to another sheet in the original workbook may be adjusted by Excel to refer back to the source workbook, creating an external link. For example, a total on one tab that refers to a detail tab may still calculate while the original file is available, but the exported workbook may show a link to that original file. If you need a standalone report, open the exported file and check formulas and links. You can replace formulas with their calculated values in a copy when that suits the use case, but doing so removes the ability to recalculate from the original formulas.

Named ranges, data validation sources, pivot tables, slicers, queries, and connections may rely on workbook-level definitions or external data. A copied sheet can appear intact while some of these dependencies still point to the original workbook or require a refresh. Test the specific features your sheets use rather than assuming that a visual copy is a fully independent data model.

Visible sheets, hidden tabs, and other export choices

The example deliberately skips hidden worksheets. A hidden tab may contain lookup tables, calculations, or confidential material, so exporting it without noticing can be undesirable. If you need hidden worksheets too, remove the condition that checks ws.Visible = xlSheetVisible, but first decide how the exported workbook should treat each sheet’s visibility. Excel has visible, hidden, and very hidden states, and a workbook cannot have every sheet hidden.

Chart sheets are not included because the loop is over the Worksheets collection. A chart embedded in a worksheet is different from a standalone chart sheet. If your workbook uses standalone chart sheets, they need separate handling and should be tested in a distinct export procedure.

Another choice is whether each exported file should contain formulas or values only. Keeping formulas is better when recipients need to update calculations, but may preserve references to the original workbook. Values-only output is often more portable for a fixed report, but it is no longer a live calculation sheet. The example keeps the sheet copy as Excel creates it; it does not convert formulas to values.

Common problems and safe use

  • The macro is missing or blocked: confirm the file is saved as .xlsm and that you opened it in desktop Excel. Only enable macros for workbooks from a source you trust; do not lower security settings broadly just to run an unknown file.
  • Some tabs were not exported: check whether they are hidden. The provided loop exports visible worksheets only.
  • A file fails to save: choose a folder where you have write permission, check available disk space, and try a shorter folder path. A workbook may also contain features that raise an error during copying or saving.
  • The output points to the source workbook: inspect formulas and data connections for external references. That behavior can be expected when a copied formula depends on a different original tab.
  • The macro stops partway through: the error message identifies the reported error, but files already saved may remain in the folder. Review the outputs before rerunning; the code will add numbered filenames rather than intentionally overwrite them.

VBA macros cannot be reversed with Ctrl+Z. Although this macro copies sheets and leaves the source workbook open, it creates files and changes Excel’s alert and screen-refresh settings temporarily. The error handler attempts to restore those settings if a run fails. Keep the backup and review the destination folder before distributing the results.

When a macro is not the best option

For a one-time export with only a few tabs, manually right-click a worksheet tab, choose Move or Copy, select (new book), and tick Create a copy. Save the new workbook, then repeat for each sheet. This requires more effort but gives you a chance to inspect each result before continuing.

For repeatable work, VBA is convenient because it applies the same naming and folder rules to every visible worksheet. Power Query is useful for importing and transforming tabular data, but it is not a direct replacement for exporting each existing worksheet as its own workbook. If you need separate files on a schedule, a controlled macro or an approved automation workflow is more appropriate; consider access permissions and data sensitivity before automating distribution.

Frequently Asked Questions

Can Excel save each tab as a separate file without VBA?
Yes, but Excel does not provide a single standard command that exports every worksheet tab to its own workbook in one batch. You can use Move or Copy for individual sheets, or use VBA when you need to repeat the process across many tabs.

Will the macro delete or split up my original workbook?
No. It copies each visible worksheet into a new workbook and saves those copies. The original workbook remains open and its worksheets stay in place.

Will each exported file include the other tabs?
No. Each new workbook contains the copied worksheet, not all the original worksheet tabs. Formulas on that sheet can still refer to the original workbook if they depend on another tab.

Can I export hidden worksheets too?
Not with the code exactly as written; it checks for visible worksheets. You can change that logic, but review hidden content and visibility carefully before exporting it.

Can I run the macro in Excel for the web?
No. Excel for the web does not run VBA. Open the workbook in desktop Excel for Windows or Mac, and make a backup before running the macro.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.