Bulk Sheet Sorter: Organize a Chaotic 30-Tab Workpaper in Two Clicks

TL;DR: This macro sorts every visible sheet A→Z in one click, or reads a custom order from a “Sheet-Order” lookup sheet you define. Sheets you don’t list go to the end. No more hunting for “Sched-G” hiding between “Notes” and “Dashboard.”

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with multiple sheets in a non-alphabetical order
• For custom order mode: a sheet named “Sheet-Order” with one tab name per row in column A

Limitations:

• Sorts only visible sheets — hidden sheets stay where they are
• Alphabetical sort is always A→Z (case-insensitive)
• Custom order is read from column A of the “Sheet-Order” sheet — any sheets not listed there stay at the end in their current relative order
• Sheet tab order changes cannot be undone with Ctrl+Z — save first
• The “Sheet-Order” sheet is not moved during custom sort (it’s implicitly excluded since you wouldn’t typically list it in its own column A)

#The Macro

Option Explicit

Sub BulkSheetSorter()
    ' ── Bulk Sheet Sorter ────────────────────────────
    ' Sorts every visible sheet alphabetically (A→Z),
    ' or reads a custom order from the "Sheet-Order"
    ' sheet. Sheets not in the custom list stay at the
    ' end. Invisible sheets are not moved.
    ' ────────────────────────────────────────────────

    ' ── Configuration ────────────────────────────────
    Const ORDER_SHEET As String = "Sheet-Order"

    ' ── Variables ────────────────────────────────────
    Dim sortChoice As VbMsgBoxResult
    Dim ws As Worksheet, soWs As Worksheet, anchor As Worksheet
    Dim i As Long, j As Long, visCount As Long
    Dim orderData As Variant
    Dim moveCount As Long
    Dim moveMsg As String

    ' ── State management ─────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

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

    ' ── Count visible sheets ─────────────────────────
    visCount = 0
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then visCount = visCount + 1
    Next ws

    If visCount <= 1 Then
        MsgBox "Only " & visCount & " visible sheet(s). Nothing to sort.", _
               vbInformation, "Bulk Sheet Sorter"
        GoTo CleanUp
    End If

    ' ── Ask user: alphabetical or custom? ────────────
    sortChoice = MsgBox("Sort " & visCount & " visible sheet(s)?" & _
        vbCrLf & vbCrLf & _
        "Yes = Alphabetical A→Z" & vbCrLf & _
        "No = Custom order from 'Sheet-Order' sheet" & vbCrLf & _
        "Cancel = Do nothing", _
        vbYesNoCancel + vbQuestion, "Bulk Sheet Sorter")

    If sortChoice = vbCancel Then GoTo CleanUp

    If sortChoice = vbYes Then
        ' ── ALPHABETICAL A→Z ─────────────────────────
        moveCount = 0
        For i = 1 To ThisWorkbook.Worksheets.Count - 1
            If ThisWorkbook.Worksheets(i).Visible <> xlSheetVisible Then GoTo NextI
            For j = i + 1 To ThisWorkbook.Worksheets.Count
                If ThisWorkbook.Worksheets(j).Visible <> xlSheetVisible Then GoTo NextJ
                If StrComp(ThisWorkbook.Worksheets(i).Name, _
                          ThisWorkbook.Worksheets(j).Name, _
                          vbTextCompare) > 0 Then
                    ThisWorkbook.Worksheets(j).Move _
                        Before:=ThisWorkbook.Worksheets(i)
                    moveCount = moveCount + 1
                End If
NextJ:
            Next j
NextI:
        Next i
        MsgBox "Sorted " & visCount & " visible sheet(s) A→Z." & vbCrLf & _
               moveCount & " sheet(s) repositioned.", _
               vbInformation, "Done"

    Else
        ' ── CUSTOM ORDER FROM Sheet-Order SHEET ──────
        On Error Resume Next
        Set soWs = ThisWorkbook.Worksheets(ORDER_SHEET)
        On Error GoTo CleanUp

        If soWs Is Nothing Then
            MsgBox "No '" & ORDER_SHEET & "' sheet found." & vbCrLf & vbCrLf & _
                   "Create a sheet named '" & ORDER_SHEET & "' with one " & _
                   "tab name per row in column A, then run again.", _
                   vbExclamation, "Configuration Needed"
            GoTo CleanUp
        End If

        ' Read the custom order from column A (skip header if present)
        orderData = soWs.Range("A1:A" & _
            soWs.Cells(soWs.Rows.Count, 1).End(xlUp).Row).Value

        ' Use the last sheet as the starting anchor
        Set anchor = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

        moveCount = 0
        For i = UBound(orderData, 1) To LBound(orderData, 1) Step -1
            If VarType(orderData(i, 1)) = vbString Then
                If Trim(CStr(orderData(i, 1))) <> "" Then
                    On Error Resume Next
                    Set ws = ThisWorkbook.Worksheets( _
                        CStr(orderData(i, 1)))
                    On Error GoTo CleanUp

                    If Not ws Is Nothing Then
                        If ws.Visible = xlSheetVisible Then
                            ws.Move Before:=anchor
                            Set anchor = ws
                            moveCount = moveCount + 1
                        End If
                        Set ws = Nothing
                    End If
                End If
            End If
        Next i

        MsgBox "Arranged " & moveCount & " sheet(s) using " & _
               "'" & ORDER_SHEET & "'." & vbCrLf & vbCrLf & _
               UBound(orderData, 1) - moveCount & _
               " name(s) in the list were not found in the workbook.", _
               vbInformation, "Done"
    End If

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

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

#How It Works

#One message box, two modes

The MsgBox has three buttons. Yes asks for an A→Z alphabetical sort — quick, no setup, works on any workbook. No reads a custom order from a “Sheet-Order” sheet you create. Cancel bails out with no changes. This keeps all interaction in a single click — no InputBox, no code editing.

sortChoice = MsgBox("Sort " & visCount & " visible sheet(s)?" & _
    vbCrLf & vbCrLf & _
    "Yes = Alphabetical A→Z" & vbCrLf & _
    "No = Custom order from 'Sheet-Order' sheet" & vbCrLf & _
    "Cancel = Do nothing", _
    vbYesNoCancel + vbQuestion, "Bulk Sheet Sorter")

#Hidden sheets are invisible to the sorter

Before asking the question, the macro counts only xlSheetVisible sheets. If you have twelve tabs but three are hidden, the MsgBox says "Sort 9 visible sheet(s)?" and only those nine participate. Hidden sheets stay exactly where you left them. This is intentional — if you hid a sheet, you probably don’t want it alphabetized into the middle of your visible workflow.

#Alphabetical sort uses a stable bubble sort

Two nested loops compare every visible sheet to every other visible sheet. When sheet B sorts before sheet A, it calls .Move Before:=A. The StrComp with vbTextCompare makes the sort case-insensitive — “summ” and “Summ” sort together. The move counter in the final message lets you verify the work: "18 sheet(s) repositioned" tells you the sorter actually rearranged things.

#Custom order reads the list backwards

This is the key design decision. The “Sheet-Order” sheet has one tab name per row in column A. The macro reads the list and processes it bottom-to-top:

For i = UBound(orderData, 1) To LBound(orderData, 1) Step -1
    ...
    ws.Move Before:=anchor
    Set anchor = ws
Next i

Why backwards? Because .Move Before pushes sheets to the front of the anchor. If we process the list top-to-bottom, the sheets end up in reverse order. By walking the list backwards, the last sheet in the list gets moved first (to the front of the anchor), then the second-to-last gets moved in front of it, and so on. The result: sheets appear in the exact top-to-bottom order from your “Sheet-Order” list.

The anchor starts as the last sheet in the workbook. Each moved sheet becomes the new anchor, so subsequent moves stack up in front. Sheets you didn’t list stay at the end, behind everything.

#What happens to the “Sheet-Order” sheet itself

The “Sheet-Order” sheet follows the same rules as every other sheet. If you include "Sheet-Order" in column A of the Sheet-Order sheet, it gets arranged too. If you don’t, it stays wherever it was. Since the lookup reads the list bottom-to-top, you’ll typically want to leave it off the list — or put it last — so it ends up at the end.

#Why a message box at the end

Silent macros are dangerous. The message box tells you exactly what happened so you can spot-check before moving on. In custom mode, it also tells you how many names in the list weren’t found — "2 name(s) in the list were not found" — which catches typos and renamed sheets.

#Before you run: save first

VBA has no undo button. Once sheets are rearranged, there’s no Ctrl+Z for tab order. The standard approach:

1. Save the workbook right before running — one click, total safety net
2. Work on a copy if you’re testing — File → Save As first
3. Review the MsgBox count matches expectations — if the workbook has 20 visible sheets and the message says 20 sorted, you know everything was covered

#Adapt It