Create hyperlink to another sheet

Question

The first sheet of my workbook is like a contents page. Each cell in column A holds an IP address string. For each IP address string, there is a worksheet named with the IP address.

I want to turn the string in the cells in column A into hyperlinks to their corresponding sheets (cell A1 of the destination sheet).

I only need the VBA line that makes the hyperlink; I can figure out the looping, etc. Remember that the name of the sheet to be linked to is the same as the value of the cell that will become the link.

Research has only brought up forum posts that give a stack of code without explaining any of it.

Solution 1

I recorded a macro making a hiperlink. This resulted.

ActiveCell.FormulaR1C1 = "=HYPERLINK(""[Workbook.xlsx]Sheet1!A1"",""CLICK HERE"")"

Solution 2

This is the code I use for creating an index sheet.

Sub CreateIndexSheet()
    Dim wSheet As Worksheet
    ActiveWorkbook.Sheets.Add(Before:=Worksheets(1)).Name = "Contents" 'Call whatever you like
    Range("A1").Select
    Application.ScreenUpdating = False 'Prevents seeing all the flashing as it updates the sheet
    For Each wSheet In Worksheets
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & wSheet.Name & "'" & "!A1", TextToDisplay:=wSheet.Name
        ActiveCell.Offset(1, 0).Select 'Moves down a row
    Next
    Range("A1").EntireColumn.AutoFit
    Range("A1").EntireRow.Delete 'Remove content sheet from content list
    Application.ScreenUpdating = True
End Sub

Solution 3

Something like the following will loop through column A in the Control sheet and turn the values in the cells into Hyperlinks. Not something I've had to do before so please excuse bugs:

Sub CreateHyperlinks()

Dim mySheet As String
Dim myRange As Excel.Range
Dim cell As Excel.Range
Set myRange = Excel.ThisWorkbook.Sheets("Control").Range("A1:A5") '<<adjust range to suit

For Each cell In myRange
    Excel.ThisWorkbook.Sheets("Control").Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:=cell.Value & "!A1" '<<from recorded macro
Next cell

End Sub

Solution 4

The "!" sign is the key element. If you have a cell object (like "mycell" in following code sample) and link a cell to this object you must pay attention to ! element.

You must do something like this:

.Cells(i, 2).Hyperlinks.Add Anchor:=.Range(Cells(i, 2).Address), Address:="", _
     SubAddress:= "'" & ws.Name & "'" & _
     "!" & mycell.Address

Solution 5

This macro adds a hyperlink to the worksheet with the same name, I also modify the range to be more flexible, just change the first cell in the code. Works like a charm

Sub hyper()
 Dim cl As Range
 Dim nS As String

 Set MyRange = Sheets("Sheet1").Range("B16")
 Set MyRange = Range(MyRange, MyRange.End(xlDown))

 For Each cl In MyRange
  nS = cl.Value
  cl.Hyperlinks.Add Anchor:=cl, Address:="", SubAddress:="'" & nS & "'" & "!B16", TextToDisplay:=nS
 Next
End Sub

View more solutions

Comments

[David Andrei Ned]

How could I have the macro link to a sequence of cells? for example in C1:C500 I have the index location of the cell I want referenced, so if C1 says "20" the macro should link the A1 to B20, if C2 says "30", link A2 to B30 etc. ?

[user4074875]

Thanks @Lee I've adopted this into my templates now. I made one change - wrapping the worksheet name with a single quote in case users create sheets with spaces which breaks the hyperlink. ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & wSheet.Name & "'" & "!A1", TextToDisplay:=wSheet.Name