VBA for Excel - link cell value to workshet via Hyperlink

[vik2slick]

Hi,

I have a workbook which has a worksheet named 'Index' that has Worksheet tab names in cells B5 to B44. How do I link each cell to a matching worksheet tab name without doing this manually?

Thank you in advance!

 

[Michael M]

Maybe this
Change the Worksheet number in the code to the "Index" sheet number

Sub MM1()
For r = 5 To 44
Worksheets([color=red]1[/color]).Hyperlinks.Add anchor:=Worksheets([color=red]1[/color]).Cells(r, 1), Address:="", SubAddress:="'" & Worksheets([color=red]1[/color]).Cells(r, 1).Value & "'!B" & r & ""
Next r
End Sub

 

[Michael M]

Sorry Typo in Previous Post....use this instead

Sub MM1()
For r = 5 To 44
Sheets("Index").Hyperlinks.Add anchor:=Worksheets("Index").Cells(r, 2), Address:="", SubAddress:="'" & Worksheets("Index").Cells(r, 1).Value & "'!B" & r & ""
Next r
End Sub

 

[vik2slick]

Hyperlink was created but I get an error when I click on the hyperlink = 'reference isn't valid' error

 

[Michael M]

Try

Sub MM1()
For r = 5 To 44
Sheets("Index").Hyperlinks.Add anchor:=Worksheets("Index").Cells(r, 2), Address:="", SubAddress:="'" & Worksheets("Index").Cells(r, 2).Value & "'!B" & r & ""
Next r
End Sub

 

[vik2slick]

Thank you - that worked!!

 

[vik2slick]

Michal,

When I click on the hyperlink for let's say cell B33, it takes me to the matching worksheet name but to cell B33. Anyway to go to that worksheet but to cell A1?

 

[Michael M]

Change to

Sub MM1()
For r = 5 To 44
Sheets("Index").Hyperlinks.Add anchor:=Worksheets("Index").Cells(r, 2), Address:="", SubAddress:="'" & Worksheets("Index").Cells(r, 2).Value & "'!A1"
Next r
End Sub

 

[vik2slick]

Thank you!

 

[Michael M]