[excel] Create hyperlink to another sheet

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.

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

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

In my implementation, the cell I was referencing could have been several options. I used the following format where 'ws' is the current worksheet being edited

For each ws in Activeworkbook.Worksheets
    For i…
       For j...
...
ws.Cells(i, j).Value = "=HYPERLINK(""#'" & SHEET-REF-VAR & "'!" & CELL-REF-VAR & """,""" & SHEET-REF-VAR & """)"

If you need to hyperlink Sheet1 to all or corresponding sheets, then use simple vba code. If you wish to create a radio button, then assign this macro to that button ex "Home Page".

Here is it:

Sub HomePage()
'
' HomePage Macro
'


' This is common code to go to sheet 1 if do not change name for Sheet1
    'Sheets("Sheet1").Select
' OR 

' You can write you sheet name here in case if its name changes

    Sheets("Monthly Reports Home").Select
    Range("A1").Select

End Sub

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

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