In an earlier post I shared some VBA code to sort sheets by their colour. In this post I have tweaked the code to sort the sheets by their name.

The code is very similar to the previous code with a single change. See the macro warning below.

Sub ShortSheetsByName()
' sorts sheets based on their name
'  numbers listed first
Dim lCount As Long
Dim x As Long
Dim y As Long

lCount = Sheets.count

For x = 1 To lCount - 1
       
    For y = x + 1 To lCount
    
        If LCase(Sheets(x).Name) > LCase(Sheets(y).Name) Then
          
            Sheets(y).Move before:=Sheets(x)
            
        End If
        
    Next y

Next x

End Sub 

The line that changed is.

If LCase(Sheets(x).Name) > LCase(Sheets(y).Name) Then

This line was previously comparing each sheet’s ColorIndex numbers.

I had to add the LCase function because uppercase and lowercase letters are sorted differently. Uppercase letters are listed before lowercase letters. By converting all the names to lowercase the letters are treated the same.

Macro Warning: Macros cannot be undone. Macros also clear the Undo list so you can’t undo anything you did before you ran the macro. So always save your file before running a macro so you can close and not save to return to the pre-macro state if something goes wrong.

Uppercase and lowercase

Interesting fact – the names uppercase and lowercase came from where they were stored for use in printing presses

Lowercase letters were used much more often, so they were stored in the easy to access bottom (lower) section. The capital letters were stored in the less accessible, higher (upper) section – see image below. Mind blown.