Sort Sheets Alphabetically Excel VBA Macro

Some Excel files contain many worksheets that we may need to put in order for easier access. This macro example sorts all sheets in a workbook alphabetically from A to Z. Use the code below to sort sheets alphabetically with Excel VBA.
 

Macro/VBA code:

  

  Sub SortSheetsAlphabetically()

      Dim shr1 As Integer, shr2 As Integer

      For shr1 = 1 To Sheets.Count

      For shr2 = 1 To Sheets.Count - 1

          If UCase(Sheets(shr2).Name) > UCase(Sheets(shr2 + 1).Name) Then

              Sheets(shr2).Move After:=Sheets(shr2 + 1)

          End If

      Next shr2

      Next shr1

  End Sub

Macro explained:

• First we declare two variables (shr1, shr2) that will hold a number value while looping through all sheets.
• Then we need two For Next loops in order to go through all sheets (first loop) and sort it alphabetically with respect to the other sheets each time (second loop). Sheets.Count returns the total number of sheets in the active workbook.
• The IF statement checks if the sheet name ‘Sheets(shr2).Name’ is further in the alphabet than the next sheet name ‘Sheets(shr2+1).Name’ using the UCase function. In that case, it moves that sheet after the one besides with the Move method of the Sheets object.

This is how we sort sheets alphabetically in Excel with VBA macros.

Other examples:

• How to Delete Blank Rows with Excel VBA macros
• How to Highlight Duplicates with Excel VBA macros
• How to List Unique Entries with Excel VBA macros