How to sort Worksheets using VBA in Excel – Alphabetically

Sometimes, you may be working on a workbook with too many worksheets. This may lead to a clutter of worksheets in the workbook if there is no proper management of the Worksheets. Therefore, there is a need to manage and sort the worksheets alphabetically. However, no built-in tool or feature can be used to sort the Worksheet alphabetically. Thankfully, Excel has a VBA tool that can be used to perform any changes in worksheet arrangement and sorting. Let us discuss some of the ways of sorting worksheets using VBA codes.

Using the Macro to sort worksheets alphabetically

a) Sorting from A to Z

Here are the steps to follow:

1. Open the Excel application.

2. Open the document you want to sort its Worksheet.

3. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button.

4. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.

5. Type the following code in the empty module.

Sub SortWorksheetsTabs()

Application.ScreenUpdating = False

Dim ShCount As Integer, i As Integer, j As Integer

ShCount = Sheets.Count

For i = 1 To ShCount – 1

For j = i + 1 To ShCount

If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then

Sheets(j).Move before:=Sheets(i)

End If

Next j

Next i

Application.ScreenUpdating = True

End Sub


6. While on the Visual Basic screen, press the F5 key to run the code.

b) Sorting from Z to A

Steps:

1. Open the Excel application.

2. Open the document you want to sort its Worksheet.

3. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button.

4. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.

5. Type the following code in the empty module.

'This code will sort the worksheets alphabetically from Z to A'
Sub SortWorksheetsTabs()
Application.ScreenUpdating = False
Dim ShCount As Integer, i As Integer, j As Integer
ShCount = Sheets.Count
For i = 1 To ShCount - 1
    For j = i + 1 To ShCount
        If UCase(Sheets(j).Name) > UCase(Sheets(i).Name) Then
            Sheets(j).Move before:=Sheets(i)
        End If
    Next j
Next i
Application.ScreenUpdating = True
End Sub


					

6. While on the Visual Basic screen, press the F5 key to run the code.


					

Using View Code to sort Sheets alphabetically

Steps to follow:

1. Open the Excel application.

2. Open the Excel document you want to sort.

3. Right-click on one of the shown sheets, and select the View Code button from the menu.

4. In the Visual Basic screen, type the following code in the empty module.

'This code will sort the worksheets alphabetically from A to Z'

Sub SortWorksheetsTabs()

Application.ScreenUpdating = False

Dim ShCount As Integer, i As Integer, j As Integer

ShCount = Sheets.Count

For i = 1 To ShCount – 1

For j = i + 1 To ShCount

If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then

Sheets(j).Move before:=Sheets(i)

End If

Next j

Next i

Application.ScreenUpdating = True

End Sub


5. While on the Visual Basic screen, press the F5 key to run the code.