Intenta con el siguiente código que ubica el índice de hojas según lo que se indique en:
Set PivotCell = [E2]
Código:
Private Sub Worksheet_Activate()
Dim PivotCell As Range, Tmp As Range
Set PivotCell = [E2]
With PivotCell
.EntireColumn.ClearContents: .FormulaR1C1 = "Índice"
.Font.Bold = True: .HorizontalAlignment = xlCenter
End With
For Each Hoja In Application.Sheets
If ActiveSheet.Name = Hoja.Name Then GoTo Loop1
Set Tmp = PivotCell.Offset(200, 0).End(xlUp).Offset(1, 0)
Tmp = Hoja.Name
ActiveSheet.Hyperlinks.Add Anchor:=Tmp, Address:="", _
SubAddress:="'" & Hoja.Name & "'!A1"
Loop1:
Next Hoja
PivotCell.EntireColumn.AutoFit
Set Tmp = Nothing
Set PivotCell = Nothing
End Sub
No dejes de comentar si esto es lo que estás buscando.
Saludos