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