Creating a hyperlink list of sheets (Excel 2000 or 2003)

Topic

New Reply

Hi All …

I can create a list (thanks to a post here) of all the sheets in the workbook.

Sub ListSheets()
Dim i As Long
Dim n As Long
n = ActiveWorkbook.Sheets.Count

Sheets(“Navigation”).Select
For i = 1 To n
Range(“A” & 10 + i) = ActiveWorkbook.Sheets(i).Name
Next i
Range(“A” & 10 & “:A” & 10 + n).Sort Key1:=Range(“A10”)
End Sub

Unfortunately, this also lists my “hidden” sheets, which I do not want. And, because I like to “baby” my users, what I’d really like to do is to make the entries in the list hyperlinks to the sheet itself. The sheets are created dynamically in the macro …

Any hints/help, as always, is greatly appreciated.
–cat

Reply | Quote

Replies

Sub ListSheets()
    Dim i As Long
    Dim n As Long
    n = ActiveWorkbook.Sheets.Count
    
    Sheets("Navigation").Select
    For i = 1 To n
        
        If ActiveWorkbook.Sheets(i).Visible Then
            Range("A" & 10 + i) = ActiveWorkbook.Sheets(i).Name
        End If
    Next i
    Range("A" & 10 & ":A" & 10 + n).Sort Key1:=Range("A10")
    
End Sub

Reply | Quote

To add hyperlinks, change the line that adds the name in your or Peter’s code to

ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & 10 + i), _
Address:="", _
SubAddress:="'" & Sheets(i).Name & "'!A1", _
TextToDisplay:=Sheets(i).Name

Reply | Quote