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

holt3130 · Jul 23, 2013

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?

FormR · Jul 23, 2013

I don't have a VBA solution - but if you are interested then this can be done with a formular.
For example:
=HYPERLINK("#'"&A1&"'!A1",A1)

holt3130 · Jul 23, 2013

FormR said:
I don't have a VBA solution - but if you are interested then this can be done with a formular.
For example:
=HYPERLINK("#'"&A1&"'!A1",A1)

Yes that works just fine but not entirely for what I need to do.

I don't want to have to add another column so the formula can reference the names, I simple want to turn the names into hyperlinks...

I can do this easily just by clicking add hyperlink, but the list is like 500+ names long and it would take forever to to this for each individual name.

FormR · Jul 23, 2013

Give this a try:

Rich (BB code):

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.

holt3130 · Jul 23, 2013

FormR said:
Give this a try:

Rich (BB code):

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.

I think it is telling me that the subaddress is an invalid procedure call or argument.
I don't know why that would work for the anchor location but not the link location...

FormR · Jul 23, 2013

holt3130 said:
I think it is telling me that the subaddress is an invalid procedure call or argument.
I don't know why that would work for the anchor location but not the link location...

Hmm! What version on Excel are you using?

Can you record a macro of you manually adding a single hyperlink and post the code here?

holt3130 · Jul 23, 2013

Actually scratch that, It worked!

It got an argument error when it got to a name that wasn't the exact name of a sheet.

thanks FormR

Smitty · Jul 23, 2013

Welcome to the Board!

Here's some code by Excel MVP Zack Barresse for building a Table of Contents:

Sub CreateTOC() ' Code by Zack Baresse If ActiveWorkbook Is Nothing Then MsgBox "You must have a workbook open first!", vbInformation, "No Open Book" Exit Sub End If With Application .ScreenUpdating = False .DisplayAlerts = False Dim WS As Worksheet, _ ct As Chart, _ shtName As String, _ nrow As Long, _ tmpCount As Long, _ i As Long, _ numCharts As Long nrow = 3 i = 1 numCharts = ActiveWorkbook.Charts.Count On Error GoTo hasSheet Sheets("Table of Contents").Activate If MsgBox("You already have a Table of Contents page. Would you like to overwrite it?", _ vbYesNo + vbQuestion, "Replace TOC page?") = vbYes Then GoTo createNew Exit Sub hasSheet: Sheets.Add before:=Sheets(1) GoTo hasNew createNew: Sheets("Table of Contents").Delete GoTo hasSheet hasNew: tmpCount = ActiveWorkbook.Charts.Count If tmpCount > 0 Then tmpCount = 1 ActiveSheet.Name = "Table of Contents" With Sheets("Table of Contents") '.Cells.Interior.ColorIndex = 4 With .Range("B2") .Value = "Table of Contents" .Font.Bold = True .Font.Name = "Calibri" .Font.Size = "24" End With End With For Each WS In ActiveWorkbook.Worksheets nrow = nrow + 1 With WS shtName = WS.Name With Sheets("Table of Contents") .Range("B" & nrow).Value = nrow - 3 .Range("C" & nrow).Hyperlinks.Add _ Anchor:=Sheets("Table of Contents").Range("C" & nrow), Address:="#'" & _ shtName & "'!A1", TextToDisplay:=shtName .Range("C" & nrow).HorizontalAlignment = xlLeft End With End With Next WS If numCharts <> 0 Then For Each ct In ActiveWorkbook.Charts nrow = nrow + 1 shtName = ct.Name With Sheets("Table of Contents") .Range("B" & nrow).Value = nrow - 3 .Range("C" & nrow).Value = shtName .Range("C" & nrow).HorizontalAlignment = xlLeft End With Next ct End If With Sheets("Table of Contents") With .Range("B2:G2") .MergeCells = True .HorizontalAlignment = xlLeft End With With .Range("C:C") .EntireColumn.AutoFit .Activate End With .Range("B4").Select End With .DisplayAlerts = True .ScreenUpdating = True End With MsgBox "Done!" & vbNewLine & vbNewLine & "Please note: " & _ "Charts are listed after regular " & vbCrLf & _ "worksheets and will not have hyperlinks.", vbInformation, "Complete!" End Sub 

HTH,

aliwa · Oct 2, 2015

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

FormR said:
Give this a try:

Rich (BB code):

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.

FormR · Oct 2, 2015

Hi Aliwa, glad that an old thread has been of use to you - welcome to MrExcel

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

holt3130

New Member

Excel Facts

FormR

MrExcel MVP

holt3130

New Member

FormR

MrExcel MVP

holt3130

New Member

FormR

MrExcel MVP

holt3130

New Member

Smitty

Legend

aliwa

New Member

FormR

MrExcel MVP

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Legend

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock