Forum Discussion

Copper Contributor

Mar 26, 2021

Alphabetized Index of Worksheets

I am trying to index a large workbook, but I want the index to automatically alphabetize itself for easy usage. (I know this can be done because one-upon-a-time I found it on a forum. But I lost it...

Silver Contributor

Mar 26, 2021

SRLDavis Alternatively you could add this between the lines: Next wSheet and End Sub

With Me.Sort

.SortFields.Clear

.SortFields.Add Key:=Range(Cells(2,1),Cells(M,1)), _

SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

.SetRange Range(Cells(1,1),Cells(M,1))

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

BUT i would also recommend that you change the Sub from _Activate to either a general function maybe as a module and activate it using a button or at least make it a _Change sub and then have a special cell on the sheet you type in to update. Presently every time you switch to that sheet you are recreating all those links and updating that index.