Excel VBA to Sort Sheets by Name

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.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.