Alphabetized Index of Worksheets

[SRLDavis]

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!)  I have the  formula below to create the index with hyperlinks to click back and forth between pages.  

I am very code-illiterate so I'll need to know the code and exactly where to paste it into the formula.  Any help is greatly appreciated!

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim M As Long
M = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
M = M + 1
With wSheet
.Range("H1").Name = "Start" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("H1"), Address:="", SubAddress:="Index", TextToDisplay:="Back to Index"
End With
Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1), Address:="", SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
End Sub

[#2]

Perhaps you can use my free sheettools add-in, which inserts a Table of Content:

https://jkp-ads.com/downloadnl.asp#SheetTools

It does not sort them, but once the table is there, it is just one click to sort it.

[#3]

@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.  

[#4]

@SRLDavis 

I think this also could work for you. Add the sheet names to an arraylist, sort, dump the sheet names onto the worksheet, then convert them to hyperlinks.

Private Sub Worksheet_Activate()
     Dim wSheet As Worksheet
     Dim cell As Range
     Dim arrList As Object: Set arrList = CreateObject("System.Collections.ArrayList")
          
     
     With Me
          .Columns(1).ClearContents
          .Cells(1, 1) = "INDEX"
          .Cells(1, 1).Name = "Index"
     
     
          For Each wSheet In Worksheets
               If wSheet.Name <> Me.Name Then
                    arrList.Add wSheet.Name
                         
                    With wSheet
                         .Range("H1").Name = "Start" & wSheet.Index
                         .Hyperlinks.Add Anchor:=.Range("H1"), Address:="", SubAddress:="Index", TextToDisplay:="Back to Index"
                    End With
               End If
          Next wSheet
          
          arrList.Sort
          
          With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
               With .Resize(arrList.Count, 1)
                    .Value = Application.Transpose(arrList.toarray)
                    
                    For Each cell In .Cells
                         Me.Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:="Start" & Worksheets(cell.Value).Index, TextToDisplay:=cell.Value
                    Next cell
               End With
          End With
     End With
     
End Sub