Create hyperlink to another sheet

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