Sort Sheets Alphabetically

[jamescooper]

Hello, so I have the following code which successfully sorts the sheets in a workbook alphabetically when run.

I would like to make a couple of alterations to it. I would like it to:

1. Run automatically upon change to the number of tabs, e.g. when a sheet is added, and
2. Only to apply to sheets after a sheet called "Lookup"

Any thoughts? Many thanks.

Sub Sort_Tabs_Alphabetically()
 
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
 
End Sub

 

[gallen]

Hello, this sorts only those after a sheet called "Lookup".

As for running when you add a sheet, I'm sure you mean at the point you rename the sheet?

If so it isn't straight forward. There's no built in event for sheet name change. However I saw this thread that has a work around:

https://stackoverflow.com/questions/1939919/rename-worksheet-event-in-excel

Sub Sort_Tabs_Alphabetically()


For i = Sheets("Lookup").Index + 1 To Application.Sheets.Count
    For j = Sheets("Lookup").Index + 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
 
End Sub

 

[jamescooper]

Hello, this sorts only those after a sheet called "Lookup".

As for running when you add a sheet, I'm sure you mean at the point you rename the sheet?

If so it isn't straight forward. There's no built in event for sheet name change. However I saw this thread that has a work around:

https://stackoverflow.com/questions/1939919/rename-worksheet-event-in-excel

Sub Sort_Tabs_Alphabetically()


For i = Sheets("Lookup").Index + 1 To Application.Sheets.Count
    For j = Sheets("Lookup").Index + 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
 
End Sub

Thanks Gallen, it is OK I can set it to run after a new sheet is created in my preceding macro.