Create 100 hyperlinks at once.

[rikvny02]

Hi i have a master sheet with a list of around 100 Employees. Each employee has a tab with the same name. I would like to hyperlink each cell in column E to its corresponding tab. I found the code below and it looks like it should work but it is not. Any help would be greatful.

Sub Hyperlink_Sheets_Names()
Dim cell As Range, ws As Worksheet
With Sheets("OPTIONS") 'Sheet with the hyperlink sheet names
On Error Resume Next
For Each cell In .Range("e1", .Range("e" & Rows.Count).End(xlUp)) 'Loop for each used cell in column E
If cell.Value <> "" Then
Set ws = Nothing
Set ws = Sheets(cell.Value)
If Not ws Is Nothing Then
Hyperlinks.Add anchor:=cell, Address:="", SubAddress:=("'" & cell.Value & "'!a1")
End If
End If
Next cell
On Error GoTo 0
End With
End Sub

 

[Scott Huish]

How about:

Sub Macro1()
Dim c As Range
For Each c In Range("E1:E" & Range("E" & Rows.Count).End(xlUp).Row)
    If c.Hyperlinks.Count = 0 And c <> "" Then c.Hyperlinks.Add c, "", c & "!A1", , c.Text
Next
End Sub

 

[rikvny02]

How about:

Sub Macro1()
Dim c As Range
For Each c In Range("E1:E" & Range("E" & Rows.Count).End(xlUp).Row)
    If c.Hyperlinks.Count = 0 And c <> "" Then c.Hyperlinks.Add c, "", c & "!A1", , c.Text
Next
End Sub

Thank you. Your my new best friend. lol