我录制了一个宏来创建hiperlink。结果就是这样。

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

这是我用来创建索引表的代码。

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

类似于下面的操作将循环遍历控制表中的列A，并将单元格中的值转换为超链接。这不是我以前必须做的事情，所以请原谅bug：

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

“！”符号是关键元素。如果你有一个cell对象（如下面的代码示例中的“mycell”），并将一个cell链接到这个对象，你必须注意！元素
你必须这样做：

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

如果您需要将Sheet1超链接到所有或相应的工作表，则使用简单的vba代码。如果你想创建一个单选按钮，那么把这个宏分配给那个按钮，比如“主页”。
这就是它：

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

这个宏添加了一个超级链接到工作表的同名，我还修改了范围，使其更加灵活，只需要改变代码中的第一个单元格。效果很好

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

在我的实现中，我引用的单元格可以是几个选项。我使用了以下格式，其中“ws”是当前正在编辑的工作表

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 & """)"