Sort Sheet Tabs Alphabetically – Excel Macro

In this blog post, we will create a macro to sort sheet tabs alphabetically with the click of a button.
Unfortunately Excel has no feature built-in to sort sheet tabs. But we can create our own, and with VBA set it up however we like.

This macro will be quite simple, but if you are not very familiar with Excel VBA you can sign up for the online Excel VBA course for beginners. This will help to get a better grasp of some of the techniques involved.

Watch the video below to see the macro being written and then tested, or read on to get the code and a written description.

Watch the Video

The Sort Sheets VBA code

Below is the code used in this macro to sort the sheet tabs.

Sub SortSheets()
Dim s As Integer
Dim i As Integer
For s = 1 To ActiveWorkbook.Sheets.Count
For i = 1 To ActiveWorkbook.Sheets.Count - 1
If UCase$(Sheets(i).Name) > UCase$(Sheets(i + 1).Name) Then
Sheets(i).Move after:=Sheets(i + 1)
End If
Next i
Next s
End Sub

How it Works

The code uses two For loops.

The interior loop which begins with For i = 1 loops through each sheet in the workbook testing if the sheet is greater than the sheet to its right, and moving it if so.

The exterior loop ensures that this process is repeated for as many times as there are sheets to be assured they have all been moved if necessary.

The Ucase$ in the If statement ensures the sheet names are in the same case for testing. Otherwise a sheet with an uppercase starting letter would be viewed differently to one with a lowercase starting letter.

The Ucase$ function converts them to uppercase. The $ sign ensures that the values is returned as a string, whilst Ucase alone returns it as a variant. This keeps our macro lean. With a macro of this size, it was not too important. But was nice practice.

Last Words

This is a simple macro to accomplish the task of sorting sheet tabs alphabetically. The code can be altered slightly to achieve other goals such as sorting descending, or ignoring specific sheet names, or having a custom list such as names of months.

This is a nice challenge for those wanting to learn VBA.