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