Sub HyperLink_SheetsToIndex()
' Local Variables
Dim wks As Worksheet
Dim rngLinkCell As Range
Dim strSubAddress As String, strDisplayText As String
' Step 1 : Loop through all worksheets
' 1a : Clear all current hyperlinks
Worksheets("Sheet Index").Range("A:A").ClearContents
' 1b : Create Linked index list
For Each wks In ActiveWorkbook.Worksheets
Set rngLinkCell = Worksheets("Sheet Index").Range("A65536").End(xlUp)
If rngLinkCell <> "" Then Set rngLinkCell = rngLinkCell.Offset(1, 0)
strSubAddress = "'" & wks.Name & "'!A1"
strDisplayText = "HyperLink : " & wks.Name
Worksheets("Sheet Index").Hyperlinks.Add Anchor:=rngLinkCell, Address:="", SubAddress:=strSubAddress, TextToDisplay:=strDisplayText
Next wks
End Sub
ADVERTISEMENT
ADVERTISEMENT
Sheets("Sheet Index").activate
Sub SheetPopup()
Application.CommandBars("Workbook Tabs").ShowPopup
End Sub
My spreadsheet has approx 50 tabs, can I cascade them so that I don't have to scroll across all the time to find the one I want (ie, can they all be shown in the one window)
TIA
Shelly