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
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
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
7条答案
按热度按时间kqlmhetl1#
我录制了一个宏来创建hiperlink。结果就是这样。
olhwl3o22#
这是我用来创建索引表的代码。
2g32fytz3#
类似于下面的操作将循环遍历控制表中的列A,并将单元格中的值转换为超链接。这不是我以前必须做的事情,所以请原谅bug:
gywdnpxw4#
“!”符号是关键元素。如果你有一个cell对象(如下面的代码示例中的“mycell”),并将一个cell链接到这个对象,你必须注意!元素
你必须这样做:
jm2pwxwz5#
如果您需要将Sheet1超链接到所有或相应的工作表,则使用简单的vba代码。如果你想创建一个单选按钮,那么把这个宏分配给那个按钮,比如“主页”。
这就是它:
vtwuwzda6#
这个宏添加了一个超级链接到工作表的同名,我还修改了范围,使其更加灵活,只需要改变代码中的第一个单元格。效果很好
kse8i1jr7#
在我的实现中,我引用的单元格可以是几个选项。我使用了以下格式,其中“ws”是当前正在编辑的工作表