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.
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.
VBA Coding Made EasyStop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!