How to Sort Tabs / Worksheets with a VBA Macro

This article will demonstrate how to Sort Tabs / Worksheets with VBA Macros.

We can use a VBA macro to sort the sheets in our Excel workbook into alphabetical order – ascending or descending.

Writing the VBA Macro

We can write a macro within our Excel workbook if we only want to use the macro in that workbook, or we can write it within the Personal Macro workbook if we want the macro to work on all workbooks that we create.

To write the macro, we need to access the Visual Basic Editor.

In the Ribbon, select Developer > Code > Visual Basic.

 

OR

Press Alt+F11 on the keyboard

This will switch you into the Visual Basic Editor.

Note: If you don’t see the Developer Ribbon, you’ll need to enable it.

Select the VBA Project where you wish to store the macro and then, in the Ribbon, select Insert > Module.

 

 

Click in the module, and type the following code to sort tabs ascendingly:

Sub SortTabs()
  Dim iCount As Integer
  Dim x, y, z As Integer

'count how many sheets in the workbook
  iCount = ActiveWorkbook.Sheets.Count

'if only one sheet, exit the macro
  If iCount = 1 Then Exit Sub

'otherwise sort alphabetically
  For x = 1 To iCount - 1
    For y = x + 1 To iCount
      If Sheets(y).Name < Sheets(x).Name Then
        Sheets(y).Move Before:=Sheets(x)
      End If
    Next y
  Next x
End Sub

To write a macro that sorts the sheets into descending alphabetical order, you can type the following code.

Sub SortTabsDesc()
  Dim iCount As Integer
  Dim x, y, z As Integer

'count how many sheets in the workbook
  iCount = ActiveWorkbook.Sheets.Count

'if only one sheet, exit the macro
  If iCount = 1 Then Exit Sub

'otherwise sort alphabetically descending
  For x = 1 To iCount - 1
    For y = x + 1 To iCount
'this is the line that changes for descending
      If Sheets(y).Name > Sheets(x).Name Then
         Sheets(y).Move Before:=Sheets(x)
      End If
    Next y
  Next x
End Sub

Running the VBA Macro

You can run the macro from the VBA module or you can run it from Excel.

To run it from within the VBA module, click in the code and then in the Menu, select Run > Run Sub/UserForm OR press F5 (Click here for more VBE Shortcuts).

 

 

To run the macro from Excel,  press Alt+F11 or Alt+Q to switch back to Excel or in the Menu, select File > Close and Return to Microsoft Excel.

NOTE: Alt+F11 will switch back to Excel leaving the VBE open while Alt+Q will close the VBE.

In the Ribbon, select View > Macros > View Macros.

 

OR press Alt+F8.

OR if your Developer Ribbon is visible, in the Ribbon, select Developer > View Macros.

 

 

In the Macro dialog box, (1) select the macro and then (2) click Run.

 

 

Creating a shortcut for the VBA Macro

We can create shortcut keys to run our macros.

Press Alt +F8 to view the Macro dialog box, or, in the Ribbon, select View > Macros > View Macros.

Select the macro you wish to create the shortcut key, and then click Options.

 

 

Click in the Shortcut key box and then press Shift+S (or whichever other key you would like to use).  You can type in a macro description if you wish, and then click OK.

 

 

Assign another shortcut key to the 2nd macro.

 

Click OK, and then Cancel to return to Excel.

Press Ctrl+Shift+S to sort your sheets alphabetically in ascending order and then press Ctrl+Shift+D to re-sort in Descending order!

Alternatively, you can create a button to run your macro instead of a shortcut key.