Once you have more than a couple of worksheets, you need to manually arrange these. How easy would it be had there been a way to quickly sort the worksheets alphabetically in Excel. While there is no inbuilt feature way to do this, it can be done (easily) using VBA. In this tutorial, I will give you the code and the exact steps you need to follow to sort worksheets in Excel. You can tweak the code to sort the worksheets in an ascending or descending order.
VBA code to Sort Worksheets in Excel (Alphabetically)
Below is the code that will sort the worksheets in alphabetical order as soon as you run it. The above is a simple code that uses to For Next loops to analyze each worksheet against all the worksheets. It compares the name of a worksheet against all the worksheets and moves it based on its name in alphabetical order. It then moves on to the next worksheet and then checks it against all the worksheets. This process is repeated for all the worksheets and the final result is an order of worksheets sorted in alphabetical order.
A few important things to know about this code:
Sort Worksheets in Descending Order
If you want to sort worksheets in descending order, you only need to change the < (less than) sign with the > (greater than) sign. The below code would sort the worksheets in descending order:
Sort Worksheets in Ascending/Descending Order Based on User Input
You can also give the user the option to choose whether he/she wants to sort in ascending/descending order. The below code would show a message box and the user can select the order to sort. The above code when executed shows a message as shown below. It sorts based on the selection (Yes for Ascending and No for Descending). In case you click Cancel, the code stops and nothing happens.
Note: The sorting cannot be undone. In case you want to keep the original order as well, make a copy of the workbook.
Where to Put the VBA Code
Excel has a VBA backend called the VBA editor. You need to copy and paste the VBA code into the VB Editor module code window. Here are the steps to do this:
How to Run the VBA Code
In Excel, there are various ways to run the VBA code. You can run the code right from the Visual Basic Editor (also called the VB Editor). You can insert a button or a shape in the worksheet and assign the macro to it. When you click on the button, it will run the macro instantly. You can also add the macro to the Quick Access Toolbar (QAT). Now whenever you have to sort the worksheet tabs, you can just click on the macro code icon in the QAT.
How to Sort Data in Excel using VBA. How to Get the Sheet Name in Excel? Automatically Sort Data in Alphabetical Order using Formula. How to do a Multiple Level Data Sorting in Excel. An Introduction to Excel Data Sorting Options. Excel VBA Autofilter: A Complete Guide with Examples How to Group Worksheets in Excel How to Flip Data in Excel
