Split Workbook By Sheet: Export Every Tab to Its Own File in One Click

TL;DR: You have a 20-entity consolidation workbook. Each entity owner needs only their own schedule. Right-click tab → Move or Copy → new workbook → Save As → name it → close → repeat 19 more times. This macro does all 20 in 3 seconds, exporting each sheet to a clean .xlsx file in a dedicated folder.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook that has been saved at least once (the macro needs a folder path for the output files)
• Sheets you want to export — the macro skips hidden sheets by default when exporting all

What the macro does:

• Asks whether to export all visible sheets or a comma-separated list you type in
• Creates a subfolder named after your workbook (e.g., Henderson-Consolidation-sheets/) in the same folder
• Copies each selected sheet to a brand new .xlsx workbook, saves it, and closes the file — all in the background
• Reports how many files were created and where they are

Limitations:

• Exports values, formulas, and formatting — but NOT VBA code (.xlsx files are macro-free). If your sheets depend on VBA, the exported files won’t have it
• Sheet names with invalid filename characters (/ \ ? * [ ] :) get those characters replaced with underscores in the output filename
• If a file with the same name already exists in the output folder, it’s overwritten silently. Run the timestamped backup macro first if that worries you
• Only exports from ThisWorkbook — the workbook containing the macro. Store in your Personal Macro Workbook if you want to split any open workbook
• The original workbook is not modified in any way

#The Macro

Option Explicit

Sub SplitWorkbookBySheet()
    ' ── Split Workbook By Sheet ────────────────────────
    ' Exports each selected sheet to its own .xlsx file
    ' in a dedicated subfolder. Choose all visible sheets
    ' or pick specific ones by name.
    '
    ' Non-destructive — creates copies, never touches
    ' the original workbook.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const SUBFOLDER_SUFFIX As String = "-sheets"

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim outFolder As String
    Dim userChoice As VbMsgBoxResult
    Dim sheetList As String
    Dim names() As String
    Dim i As Long, count As Long
    Dim safeName As String

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Pre-flight check ───────────────────────────────
    If Len(ThisWorkbook.Path) = 0 Then
        MsgBox "Save the workbook first. Macro needs a " & _
               "folder path for the output files.", _
               vbExclamation, "Split Workbook"
        GoTo CleanUp
    End If

    outFolder = ThisWorkbook.Path & "\" & _
        Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1) & _
        SUBFOLDER_SUFFIX

    ' ── Ask: all sheets or specific? ───────────────────
    userChoice = MsgBox("Export ALL visible sheets?" & _
        vbCrLf & vbCrLf & _
        "Yes = All visible sheets" & vbCrLf & _
        "No  = Pick specific sheets" & vbCrLf & _
        "Cancel = Quit", vbYesNoCancel + vbQuestion, _
        "Split Workbook")

    If userChoice = vbCancel Then GoTo CleanUp

    ' ── Create output folder ───────────────────────────
    On Error Resume Next
    MkDir outFolder
    On Error GoTo CleanUp

    count = 0

    If userChoice = vbYes Then
        ' ── Export all visible sheets ──────────────────
        For Each ws In ThisWorkbook.Worksheets
            If ws.Visible = xlSheetVisible Then
                safeName = Replace(Replace(Replace(Replace( _
                    Replace(Replace(Replace(ws.Name, _
                    "/", "_"), "\", "_"), "?", "_"), _
                    "*", "_"), "[", "_"), "]", "_"), ":", "_")
                ws.Copy
                ActiveWorkbook.SaveAs outFolder & "\" & _
                    safeName & ".xlsx", xlOpenXMLWorkbook
                ActiveWorkbook.Close False
                count = count + 1
            End If
        Next ws
    Else
        ' ── Export user-specified sheets ───────────────
        sheetList = InputBox("Enter sheet names to export," & _
            " separated by commas:" & vbCrLf & _
            "(e.g., Entity-A, Entity-B, Entity-C)", _
            "Choose Sheets")
        If sheetList = "" Then GoTo CleanUp

        names = Split(sheetList, ",")
        For i = LBound(names) To UBound(names)
            Dim target As String
            target = Trim(names(i))
            On Error Resume Next
            Set ws = ThisWorkbook.Worksheets(target)
            On Error GoTo CleanUp
            If Not ws Is Nothing Then
                safeName = Replace(Replace(Replace(Replace( _
                    Replace(Replace(Replace(ws.Name, _
                    "/", "_"), "\", "_"), "?", "_"), _
                    "*", "_"), "[", "_"], "]", "_"), ":", "_")
                ws.Copy
                ActiveWorkbook.SaveAs outFolder & "\" & _
                    safeName & ".xlsx", xlOpenXMLWorkbook
                ActiveWorkbook.Close False
                count = count + 1
                Set ws = Nothing
            End If
        Next i
    End If

    ' ── Report results ─────────────────────────────────
    If count = 0 Then
        MsgBox "No sheets were exported.", vbInformation, _
               "Split Workbook"
    Else
        MsgBox "Created " & count & " file(s) in:" & _
               vbCrLf & vbCrLf & outFolder, _
               vbInformation, "Split Workbook"
    End If

CleanUp:
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#The MsgBox-as-menu pattern

This macro doesn’t ask you to edit constants or open a UserForm. It puts all decisions in front of you at runtime. The first thing you see is a three-button MsgBox:

Export ALL visible sheets?

Yes = All visible sheets
No  = Pick specific sheets
Cancel = Quit

vbYesNoCancel gives three choices. vbYes triggers the “all sheets” path. vbNo triggers the “pick sheets” path — it asks for a comma-separated list in a follow-up InputBox. vbCancel exits cleanly without creating anything.

This pattern — one MsgBox branching into different code paths — is how you build flexible macros without forcing the user into the VBA editor.

#ws.Copy is the engine

VBA’s Worksheet.Copy method, called with no arguments, does three things at once: it copies the sheet, creates a brand new workbook containing only that sheet, and makes the new workbook the active workbook. It’s the equivalent of right-click → Move or Copy → New workbook → OK, but from code.

ws.Copy                                ' Sheet → new workbook
ActiveWorkbook.SaveAs outFolder & "\" & _
    safeName & ".xlsx", xlOpenXMLWorkbook  ' Save as .xlsx
ActiveWorkbook.Close False                 ' Close without saving again

After ws.Copy, ActiveWorkbook refers to the new single-sheet workbook (not the original). The macro saves it immediately, closes it, and loops to the next sheet. The original workbook never loses focus — you won’t even see the new workbooks flash because ScreenUpdating is off.

The False parameter on .Close means “don’t save changes” — because we already saved with SaveAs. Without this, Excel would ask “Save changes?” for every file. With DisplayAlerts = False, it would close without asking, but explicitly providing False is cleaner.

#Filename sanitization: the 7 illegal characters

Windows filenames can’t contain \/?:*[]". Excel sheet names CAN contain some of these — especially brackets [ ] (used for grouping in multi-entity workpapers) and slashes / (used in date-formatted tab names).

If you try to SaveAs with a filename containing [, Excel raises error 1004 and the macro stops. The fix is a chain of Replace() calls that swap each illegal character for an underscore:

safeName = Replace(Replace(Replace(Replace( _
    Replace(Replace(Replace(ws.Name, _
    "/", "_"), "\", "_"), "?", "_"), _
    "*", "_"), "[", "_"), "]", "_"), ":", "_")

This converts Sched-A [Final] to Sched-A _Final_ and FY 2023/2024 to FY 2023_2024. The output filename never matches the original sheet name exactly — it’s close enough that anyone can identify which file came from which sheet.

Seven characters, seven Replace calls, one safeName. This appears twice in the code (once in each branch) because the “all sheets” and “pick sheets” paths operate on different loop structures but need the same sanitization.

#Why .xlsx and not .xlsm

The macro exports to xlOpenXMLWorkbook (.xlsx), not xlOpenXMLWorkbookMacroEnabled (.xlsm). This is deliberate:

1. Security. The exported files are going to entity owners, clients, and partners. You don’t want to ship VBA code when the recipient only needs data. .xlsx files can’t contain macros — they’re safer by design
2. Surface area. If your original workbook has 17 modules of tax calculation VBA, exporting to .xlsm would copy all of it into every output file, multiplying the total file size by the number of sheets. .xlsx strips the code, keeping each file lean
3. Compatibility. .xlsx opens on macOS, Excel Online, and Google Sheets without security warnings. .xlsm requires macro support

The tradeoff: any formulas that reference other sheets in the original workbook will become #REF! in the exported file (because those sheets no longer exist in the single-sheet workbook). The exported file is a data snapshot, not a functional model. If you need cross-sheet formulas to survive the export, use the paste-values-everywhere macro first to convert formulas to values, then run this macro.

#The MkDir guard with On Error Resume Next

MkDir creates a folder. If the folder already exists, MkDir raises error 75 (“Path/File access error”) and the macro would jump to CleanUp. But the folder already existing is not an error — it’s the expected state when you run the macro a second time.

On Error Resume Next
MkDir outFolder
On Error GoTo CleanUp

This three-line pattern says: “try to create the folder, ignore the error if it already exists, then restore normal error handling.” It’s one of the few places in this blog’s macros where On Error Resume Next is appropriate — because we’re not ignoring a problem, we’re handling a specific, expected condition that VBA’s API can’t distinguish from a real error.

#Adapt It