Thread: Adding a Hyperlink to a List of Worksheet Names

Hello,

I have a workbook with about 100 worksheets. I have a macro that lists the names of the worksheets (below) and I am trying to add a hyperlink to each of the worksheet names, but not having any luck. Any thoughts are greatly appreciated.

Regards,
Doug

Sub SheetNames()
'This sub creates a list of all the worksheet names in a workbook.

    Columns(1).Insert
    For i = 1 To Sheets.Count
         Cells(i, 1) = Sheets(i).Name
    Next i
End Sub

This should do the trick.

Sub SheetNames()
'This sub creates a list of all the worksheet names in a workbook.

    Columns(1).Insert
    For i = 1 To Sheets.Count
         Cells(i, 1) = Sheets(i).Name
         ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="", SubAddress:=Sheets(i).Name & "!A1", TextToDisplay:=Sheets(i).Name
    Next i
    
End Sub

Hello rdgreen,

This will add links to "Sheet1" starting at "A1" to every other worksheet in the workbook. The link will take you to cell "A1" of the worksheet. You can change "Sheet1" to match the name of your worksheet and the cell to go to on the linked sheet. They are marked in red.

Sub AddLinks()

  Dim MainWks As Worksheet
  Dim R As Long
  Dim Wks As Worksheet
  
    Set MainWks = Worksheets("Sheet1")
    
    For Each Wks In Worksheets
      If Wks.Name <> MainWks.Name Then
        R = R + 1
        With MainWks
          .Hyperlinks.Add Anchor:=.Cells(R, "A"), _
                          Address:="", _
                          SubAddress:=Wks.Name & "!$A$1"
        End With
      End If
    Next Wks
    
End Sub

Sincerely,
Leith Ross