Forum Discussion

SRLDavis's avatar
SRLDavis
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!)  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

excel
Macros and VBA
office 365

3 Replies

  • JMB17's avatar
    JMB17
    Bronze Contributor
    Mar 26, 2021

    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

     

  • mtarler's avatar
    mtarler
    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.  

Resources