Sort Worksheets by Name

There's no built-in tool to alphanumerically in Excel. If you want to by name, you need to drag and drop the sheet tabs on the sheet tab bar. If you have multiple worksheets, it seems very boring to do it manually. In this situations, you can use this simple macro.

Sort Worksheets by Name VBA Code

Sub SortWorksheetsByName()
'Step 1: Declare your Variables
    Dim CurrentSheetIndex As Integer
    Dim PrevSheetIndex As Integer
'Step 2: Turn screen updating off to speed up your macro code
    Application. = False
'Step 3: Set the starting counts and start looping
    For CurrentSheetIndex = 1 To Sheets.Count
    For PrevSheetIndex = 1 To CurrentSheetIndex - 1
'Step 4: Check Current Sheet against Previous Sheet
    If UCase(Sheets(PrevSheetIndex).Name) > UCase(Sheets(CurrentSheetIndex).Name) Then
'Step 5: If Move Current sheet Before Previous
    Sheets(CurrentSheetIndex).Move Before:=Sheets(PrevSheetIndex)
    End If
'Step 6 Loop back around to iterate again
    Next PrevSheetIndex
    Next CurrentSheetIndex
'Step 7: Turn screen updating on
    Application. = True
End Sub

This macro 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 (by alphabetically), the macro moves the current sheet before it. By the time all the iterations are done, you’ve got a sorted workbook!

Note that this macro is doing a text-based sort, so you may not get the results you were expecting when working with number-based sheet names. For instance, Sheet10 comes before Sheet2 because textually, 1 comes before 2. Excel doesn’t do the numbers-based sorting that says 2 comes before 10.

How This Macro Works

  1. In 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, we use Application.ScreenUpdating = False to turn screen updating off to speed up this macro code.
  3. In Step 3, 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.
  4. In Step 4, 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.
  5. Step 5 is reached only if the previous sheet name is greater than the current sheet name. In this step, we use the to move the current sheet before the previous sheet.
  6. In Step 6, 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.
  7. Finally, you should turn screen updating on use Application.ScreenUpdating = True.

How to Use This Macro

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.xlsb in the Project window.
  3. Choose Insert ➜ Module.
  4. Type or paste the code in the newly created module.
    Insert a Module in Personal Macro Workbook

Don't see personal.xlsb in your project window?

If you don't see personal.xlsb in your project window, 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 box.

Store Macro In Personal Macro Workbook

Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.

Comments

  1. says

    Hi there,I log on to your blogs named “Sort Worksheets by Name – Excel Macros” like every week.Your story-telling style is awesome, keep doing what you’re doing! And you can look our website about powerful free love spells.

Leave a Reply

Your email address will not be published. Required fields are marked *