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.