Hyperlink Macro that converts a list of sheet names to hyperlinks to the sheets

[holt3130]

Hey,

I am having trouble getting a macro to do what I want.

In column A i have a list of the sheet names in the workbook

I need a macro that will take the text from the cell and convert it to a hyperlink to its corresponding sheet

for example in cell A1 there is the text "SP5M"
there is also a sheet named "SP5M"
I want a macro to take the text from the cell that I have selected, tack on the "!A1" so it becomes an address for the sheet and create a hyperlink that displays just the original name...

if that makes sense?

 

[JRidgeReed]

I just wanted to send out a big THANK YOU! This worked brilliantly for me.

I found this thread as it *almost* provides what I've been trying to figure out for a while. I need the macro to turn text in column B into hyperlinks to sheets which are named in column A, and I can't figure out how to adjust the above to do it (my VBA skills are extremely limited and rely on me reverse engineering similar code). Ideally I also need the macro to work in any sheet I run it in without having to adjust the macro for each sheet I need to create links in.

Can anyone help?

 

[FormR]

I found this thread as it *almost* provides what I've been trying to figure out for a while. I need the macro to turn text in column B into hyperlinks to sheets which are named in column A, and I can't figure out how to adjust the above to do it (my VBA skills are extremely limited and rely on me reverse engineering similar code). Ideally I also need the macro to work in any sheet I run it in without having to adjust the macro for each sheet I need to create links in.

Hi, you could give this a try.

Sub m()
Dim c As Range
For Each c In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
    c.Hyperlinks.Add c, "", "'" & c.Offset(, -1).Value & "'!A1", c.Value
Next c
End Sub

 

[JRidgeReed]

Hi, you could give this a try.

Sub m()
Dim c As Range
For Each c In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
    c.Hyperlinks.Add c, "", "'" & c.Offset(, -1).Value & "'!A1", c.Value
Next c
End Sub

I adjusted the cell values (including !A1 to !B1) and then it worked perfectly, thank you so much!

 

[NesYo]

Give this a try:

Sub Test()
Dim i As Long
    With Sheets("Sheet1")
        For i = 1 To .Range("A" & .Rows.Count).End(xlUp).Row
        .Hyperlinks.Add Anchor:=.Range("A" & i), Address:="", _
        SubAddress:="'" & .Range("A" & i).Value & "'!A1", TextToDisplay:=.Range("A" & i).Value
        Next i
    End With
End Sub

Change the sheet name to the sheet that contains your list.

Try on a copy of your workbook.

Need very much the same. Just tried the code in excel 2016 and it gave a Runtime error 5: Invalid procedure call or argument. Any clue?
J

 

[FormR]

Any clue?

Hi, try this alternative.

Assumes your sheet names are in column A starting on row 2

Sub m()
Dim c As Range
For Each c In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    c.Hyperlinks.Add c, "", "'" & c.Value & "'!A1", CStr(c.Value)
Next c
End Sub

 

[NesYo]

Worked great. Thank you!