Excel VBA sort worksheet tab order

This Excel VBA tutorial explains how to sort worksheet tab in alphabetic order (ascending order or descending order).

You may also want to read:

Excel Range Sort Method to sort data

Use VBA Excel Function to sort data in ascending order

Access Excel assign number for custom sorting sequence

Sort worksheet tab by name

When you have a lot of worksheets in a workbook, you may want to sort them in alphabetic order for easy searching.

As a behavior of Excel sorting, when an array is a mix of letters and numbers, letters come first in an ascending sort.

Excel VBA sort worksheet tab in ascending order

Press ALT+F11 to enter VBE, insert a new Module and then copy and paste the below codes

Sub sortAscending()
    For i = 1 To Worksheets.Count
        For j = i To Worksheets.Count
            If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then
                  Sheets(j).Move before:=Sheets(i)
             End If
        Next j
    Next i
End Sub

Result

Press F5 to run the Procedure. Now the worksheets order are in ascending order.

Excel VBA sort worksheet tab in descending order

To sort worksheet tab in descending order, slightly change the ascending code from < (smaller than) to > (larger than)

Sub sortDecending()
    For i = 1 To Worksheets.Count
        For j = i To Worksheets.Count
            If UCase(Sheets(j).Name) > UCase(Sheets(i).Name) Then
                  Sheets(j).Move before:=Sheets(i)
             End If
        Next j
    Next i
End Sub

Result

Press F5 to run the Procedure. Now the worksheets order are in descending order.

Excel VBA sort worksheet tab in custom order

This Macro makes use of my previously wrote function called wCustomSort to on order to sort worksheet name in a custom order.

Sub customSort()
    For i = 1 To Worksheets.Count
        For j = i To Worksheets.Count
            If wCustomSort(Sheets(j).Name) < wCustomSort(Sheets(i).Name) Then
                  Sheets(j).Move before:=Sheets(i)
             End If
        Next j
    Next i
End Sub


Public Function wCustomSort(Item) As Integer
    Dim arrayList As Variant
    arrayList = Array("Finance", "HR", "IT", "Legal") 'put your items here in desired order
    wCustomSort = 1000  'if the item is not defined in arrayList, assign a default order as 1000
    For i = LBound(arrayList) To UBound(arrayList)
        If arrayList(i) = Item Then
            wCustomSort = i
        End If
    Next i
End Function