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.