Alphabetized Index of Worksheets

%3CLINGO-SUB%20id%3D%22lingo-sub-2237978%22%20slang%3D%22en-US%22%3EAlphabetized%20Index%20of%20Worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2237978%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20index%20a%20large%20workbook%2C%20but%20I%20want%20the%20index%20to%20automatically%20alphabetize%20itself%20for%20easy%20usage.%26nbsp%3B%20I%20have%20the%26nbsp%3B%20formula%20below%20to%20create%20the%20index%20with%20hyperlinks%20to%20click%20back%20and%20forth%20between%20pages.%26nbsp%3B%20(I%20know%20this%20can%20be%20done%20because%20one-upon-a-time%20I%20found%20it%20on%20a%20forum.%26nbsp%3B%20But%20I%20lost%20it!)%3C%2FP%3E%3CP%3EI%20am%20very%20code-illiterate%20so%20I'll%20need%20to%20know%20the%20code%20and%20exactly%20where%20to%20paste%20it%20into%20the%20formula.%26nbsp%3B%20Any%20help%20is%20greatly%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Activate()%3CBR%20%2F%3EDim%20wSheet%20As%20Worksheet%3CBR%20%2F%3EDim%20M%20As%20Long%3CBR%20%2F%3EM%20%3D%201%3CBR%20%2F%3EWith%20Me%3CBR%20%2F%3E.Columns(1).ClearContents%3CBR%20%2F%3E.Cells(1%2C%201)%20%3D%20%22INDEX%22%3CBR%20%2F%3E.Cells(1%2C%201).Name%20%3D%20%22Index%22%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20wSheet%20In%20Worksheets%3CBR%20%2F%3EIf%20wSheet.Name%20%26lt%3B%26gt%3B%20Me.Name%20Then%3CBR%20%2F%3EM%20%3D%20M%20%2B%201%3CBR%20%2F%3EWith%20wSheet%3CBR%20%2F%3E.Range(%22H1%22).Name%20%3D%20%22Start%22%20%26amp%3B%20wSheet.Index%3CBR%20%2F%3E.Hyperlinks.Add%20Anchor%3A%3D.Range(%22H1%22)%2C%20Address%3A%3D%22%22%2C%20SubAddress%3A%3D%22Index%22%2C%20TextToDisplay%3A%3D%22Back%20to%20Index%22%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3EMe.Hyperlinks.Add%20Anchor%3A%3DMe.Cells(M%2C%201)%2C%20Address%3A%3D%22%22%2C%20SubAddress%3A%3D%22Start%22%20%26amp%3B%20wSheet.Index%2C%20TextToDisplay%3A%3DwSheet.Name%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%20wSheet%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2237978%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2238080%22%20slang%3D%22en-US%22%3ERe%3A%20Alphabetized%20Index%20of%20Worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2238080%22%20slang%3D%22en-US%22%3EPerhaps%20you%20can%20use%20my%20free%20sheettools%20add-in%2C%20which%20inserts%20a%20Table%20of%20Content%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fjkp-ads.com%2Fdownloadnl.asp%23SheetTools%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fjkp-ads.com%2Fdownloadnl.asp%23SheetTools%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20does%20not%20sort%20them%2C%20but%20once%20the%20table%20is%20there%2C%20it%20is%20just%20one%20click%20to%20sort%20it.%3C%2FLINGO-BODY%3E
Occasional Visitor

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

3 Replies
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.

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

@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