by Svetlana Cheusheva, updated on
The tutorial shows how you can quickly sort Excel worksheets in alphabetical order by using VBA code and the Workbook Manager tool.
Microsoft Excel provides a number of quick and easy ways to arrange columns or rows in alphabetical order. But there is only one method to rearrange worksheets in Excel - drag them to the desired position on the sheet tab bar. When it comes to alphabetizing tabs in a really large workbook, this may be a long and erroneous way. Looking for a time-saving alternative? There exist only two: VBA code or third-party tools.
Below you will find three VBA code examples to sort Excel sheets ascending, descending, and in either direction based on the user's choice.
Implying that you have some experience with VBA, we will only outline the basic steps to add a macro to your worksheet:
For the detailed step-by-step instructions, please see How to insert and run VBA code in Excel.
Tip. If you want to keep the macro for further usage, be sure to save your file as an Excel macro-enabled workbook (.xlsm).
Alternatively, you can download our sample Alphabetize Excel Tabs workbook, enable content if prompted, and run the desired macro directly from there. The workbook contains the following macros:
With the sample workbook downloaded and open in your Excel, open your own workbook where you want to alphabetize tabs, press Alt + F8, select the desired macro, and click Run.
This little macro arranges the sheets in the current workbook in ascending alphanumeric order, first worksheets whose names start with numbers, then sheets from A to Z.
Sub TabsAscending()
For i = 1 To Application.Sheets.Count
For j = 1 To Application.Sheets.Count - 1
If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
Sheets(j).Move after:=Sheets(j + 1)
End If
Next
Next
MsgBox "The tabs have been sorted from A to Z."
End Sub
If you want to sort your sheets in descending alphanumeric order (Z to A, then sheets with numeric names), then use the following code:
Sub TabsDescending()
For i = 1 To Application.Sheets.Count
For j = 1 To Application.Sheets.Count - 1
If UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) Then
Application.Sheets(j).Move after:=Application.Sheets(j + 1)
End If
Next
Next
MsgBox "The tabs have been sorted from Z to A."
End Sub
This macro lets your users decide how to sort worksheets in a given workbook, alphabetically from A to Z or in the reverse order.
Since the standard dialog box (MsgBox) in Excel VBA only allows choosing from a handful of predefined buttons, we will create our own form (UserForm) with three custom buttons: A to Z, Z to A, and Cancel.
For this, open the Visual Basic Editor, right-click ThisWorkbook, and click Insert > UserForm. Name your form SortOrderFrom, and add 4 controls to it: a label and three buttons:
Next, press F7 (or double-click the form) to open the Code window and paste the below code there. The code intercepts button clicks and assigns a unique tag to each button:
Depending on whether the user clicks the A to Z or Z to A button on your form, sort tabs in ascending alphabetical order (selected by default) or descending alphabetical order; or close the form and do nothing in case of Cancel. This is done with the following VBA code, which you insert in the usual way via Insert > Module.
If you are not very comfortable with VBA yet, you can simply download our Sample Workbook to Alphabetize Tabs, open it in your Excel alongside your own file where you want to sort tabs, and run the AlphabetizeTabs macro from your workbook:
Choose the preferred sort order, say, A to Z, and observe the results:
Tip. With VBA, you can also create copies of your Excel worksheets. The code is available here: How to duplicate sheet in Excel with VBA.
The users of our Ultimate Suite for Excel don't have to fiddle around with VBA - they have a multi-functional Workbook Manager at their disposal:
With this tool added to your Excel ribbon, alphabetizing tabs is done with a single button click, exactly as it should be!
If you are curious to explore this and 70+ more professional tools for Excel, a trial version of our Ultimate Suite is available for download here.
I thank you for reading and hope to see you on our blog next week!
Table of contents