我有一个包含 150 多个标签的主 Excel 表。我正在尝试创建一个搜索栏,以便我能够按字母顺序轻松查看这些命名的标签,并在单击后将我带到这些标签。
答案1
我将使用以下 VBA 代码在名为“索引”的新工作表中创建选项卡名称列表作为超链接,然后手动对其进行排序。
Sub generate_DOWN_list_with_hyperlinks_of_sheets_in_the_Active_Workbook()
Dim ws As Worksheet
If MsgBox("Do you want to create a list, starting in the active cell, of all sheets in this work book?", vbYesNo) = vbNo Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
'insert name
ActiveCell.Value = ws.Name
'insert hyperlink
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:="'" & ws.Name & "'!A1", _
TextToDisplay:=ws.Name
ActiveCell.Offset(1, 0).Activate
Next ws
End Sub