Macro to Sort Worksheets Alphabetically

These VBA macros sort the sheet tabs in your workbook A to Z or Z to A by name.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Sort sheets A to Z

Sub SortSheetsAZ()
    Dim i As Long, j As Long
    For i = 1 To Sheets.Count - 1
        For j = i + 1 To Sheets.Count
            If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then Sheets(j).Move Before:=Sheets(i)
        Next j
    Next i
End Sub

For each position, the macro moves any sheet with an earlier name in front of it. UCase makes the comparison ignore upper and lower case.

Sort sheets Z to A

Change the < to >:

Sub SortSheetsZA()
    Dim i As Long, j As Long
    For i = 1 To Sheets.Count - 1
        For j = i + 1 To Sheets.Count
            If UCase(Sheets(j).Name) > UCase(Sheets(i).Name) Then Sheets(j).Move Before:=Sheets(i)
        Next j
    Next i
End Sub

Things to know

  • Names are sorted as text, so “Sheet10” comes before “Sheet2”. Name sheets “Sheet02”, “Sheet10” to keep number order.
  • If the workbook structure is protected, unprotect it first.
  • Hidden sheets are sorted too. They just stay hidden in their new position.

Related macros