Forum Discussion
SRLDavis
Mar 26, 2021Copper Contributor
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...
JMB17
Mar 26, 2021Bronze Contributor
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