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