Macro 21: Sort Worksheets by Name
You may often need to sort worksheets alphabetically by name. You would think Excel would have a
native function to do this, but alas, it does not. If you don’t want to manually sort your spreadsheets
anymore, you can use this macro to do it for you.
How it works
This macro looks more complicated than it is. The activity in this macro is actually fairly simple. It
simply iterates through the sheets in the workbook, comparing the current sheet to the previous
one. If the name of the previous sheet is greater than the current sheet (alphabetically), the macro
moves the current sheet before it. By the time all the iterations are done, you’ve got a sorted
workbook!
Sub Macro21()
‘Step 1: Declare your Variables
Dim CurrentSheetIndex As Integer
Dim PrevSheetIndex As Integer
‘Step 2: Set the starting counts and start looping
For CurrentSheetIndex = 1 To Sheets.Count
For PrevSheetIndex = 1 To CurrentSheetIndex – 1
‘Step 3: Check Current Sheet against Previous Sheet
If UCase(Sheets(PrevSheetIndex).Name) > _
UCase(Sheets(CurrentSheetIndex).Name) Then
‘Step 4: If Move Current sheet Before Previous
Sheets(CurrentSheetIndex).Move _
Before:=Sheets(PrevSheetIndex)
End If
‘Step 5 Loop back around to iterate again
Next PrevSheetIndex
Next CurrentSheetIndex
End Sub
1. Step 1 declares two integer variables. The CurrentSheetIndex holds the index number
for the current sheet iteration, and the PrevSheetIndex variable holds the index number
for the previous sheet iteration.
2. In Step 2, the macro starts iteration counts for both variables. Note that the count for the
PrevSheetIndex is one number behind the CurrentSheetIndex. After the counts are
set, we start looping.
3. In Step 3, we check to see whether the name of the previous sheet is greater than that of the
current sheet.
In this step, note the use of the UCase function. We use this to get both names in the same
uppercase state. This prevents sorting errors due to differing case states.
4. Step 4 is reached only if the previous sheet name is greater than the current sheet name. In
this step, we use the Move method to move the current sheet before the previous sheet.
5. In Step 5, we go back around to the start of the loop. Every iteration of the loop increments
both variables up one number until the last worksheet is touched. After all
iterations have been spent, the macro ends.
How to use it
The best place to store this macro is in your Personal Macro Workbook. This way, the macro is always
available to you. The Personal Macro Workbook is loaded whenever you start Excel. In the VBE Project
window, it is named personal.xlsb.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click personal.xlb in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code in the newly created module.
If you don’t see personal.xlb in your project window, it means it doesn’t exist yet. You’ll have to
record a macro, using Personal Macro Workbook as the destination.
To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option
in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-
down list. Simply record a couple of cell clicks and then stop recording. You can discard the recorded
macro and replace it with this one.
