Excel VBA sort worksheet tab

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

Excel VBA sort worksheet tab 01

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 02

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 03

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

One thought on “Excel VBA sort worksheet tab

Leave a Reply

Your email address will not be published. Required fields are marked *