How to alphabetize Excel tabs using VBA

Keeping your data and calculations on separate tabs is easy for an organized structure, but too many tabs in a workbook can become overwhelming quickly. In this article, we're going to show you how to alphabetize Excel worksheets  in ascending or descending alphabetical order using VBA.

How to alphabetize Excel tabs

The idea behind sorting tabs is to organize your workbook and make things easier to find. We can compare names with a greater than (>) character just like comparing numbers. Since every character has a numerical ANSI code, VBA can handle this type of a comparison. You can check the links below to see the ANSI codes of alphanumerical characters.

For Windows: http://en.wikipedia.org/wiki/Windows-1252

For Mac: http://en.wikipedia.org/wiki/Mac_OS_Roman

We begin by converting all characters to upper case to eliminate the differences between the ANSI codes of upper case and lower case characters.

UCase$(Application.Sheets(j).Name)

Next, we need to compare and to move the sheet if the condition is met.

If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
  Application.Sheets(j).Move after:=Application.Sheets(j + 1)
End If

Our codes use two For Loops to check the names one by one. The nested loop allows comparing a sheet with the name of the other sheets (j loop), for the number of existing sheets (I loop).

For j = 1 To Application.Sheets.Count - 1
  If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
    Sheets(j).Move after:=Sheets(j + 1)
  End If
Next

To run this code, you need to add a module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.

Sort worksheets in ascending alphabetical order

Sub SortWorksheetsAsc()
    Application.ScreenUpdating = False
    Dim i As Integer, j As Integer
    For i = 1 To Application.Sheets.Count
        For j = 1 To Application.Sheets.Count - 1
            If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
                Application.Sheets(j).Move after:=Application.Sheets(j + 1)
            End If
        Next
    Next
    Application.ScreenUpdating = True
End Sub

Sort worksheets in descending alphabetical order

Sub SortWorksheetsDesc()
    Application.ScreenUpdating = False
    Dim i As Integer, j As Integer
    For i = 1 To Application.Sheets.Count
        For j = 1 To Application.Sheets.Count - 1
            If UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) Then
                Application.Sheets(j).Move after:=Application.Sheets(j + 1)
            End If
        Next
    Next
    Application.ScreenUpdating = True
End Sub