Return to VBA Code Examples

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.


PMW Developer VBE


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.


vbasorttabs insert module


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

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

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).


vbasorttabs run


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.


vbasorttabs ribbon view

OR press Alt+F8.

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


vbasorttabs ribbon macros


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


vbasorttabs run macro


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.


vbasorttabs 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.


vbasorttabs short cut key


Assign another shortcut key to the 2nd macro.

vbasorttabs descending


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 Easy

Stop 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! vba save as

Learn More!