VBA获取表名做成目录，

coco122222

把表格名称获取的目录的那张表格里，希望后面点击名称自动跳转到相应的表


我的附件里在网上抄了两个事件代码。但是我运行没反应。应该怎么运行啊。
或者能做个按钮也行。我不会啊。

实在不行写个代码也行。不要事件。我不会触发事件

谢谢谢谢谢谢

02761752696

1、代码放在目录表内
2、注意代码书写

3190496160

Sub 生成目录()
Dim sht As Worksheet, i As Byte, a As Byte, c As Byte
r = Cells(Rows.Count, 1).End(xlUp).Row
If r > 2 Then Range("B3:C" & r).Clear '清空数据
n = 1
For Each sht In Worksheets
    If sht.Name <> "目录" Then
        n = n + 1
        Cells(n, 1) = n - 1
        Cells(n, 2) = sht.Name
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(n, "b"), Address:="", SubAddress:="'" & sht.Name & "'!A1", TextToDisplay:=sht.Name
    End If
Next sht
End Sub

3190496160

获取表名制作成索引目录.rar (18.33 KB, 下载次数: 29)

2023-1-15 09:09 上传

点击文件名下载附件

huarong7984

所提供的方案，仅供参考

coco122222

3190496160 发表于 2023-1-15 09:10

谢谢老师。这里的厉害的老师好多

cjc209

就是超链接

cmo9020

获取表名制作成索引目录.rar (24.61 KB, 下载次数: 10)

2023-1-15 20:29 上传

点击文件名下载附件

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    SheetNames
End Sub









Sub SheetNames()
    Dim ws As Worksheet
    Dim i As Integer
    Dim ws_directory As Worksheet
    Set ws_directory = ThisWorkbook.Sheets("目录")

    i = 2

    For Each ws In ThisWorkbook.Sheets
        ws_directory.Cells(i, 2).Value = ws.Name
        ws_directory.Cells(i, 2).Hyperlinks.Add Anchor:=ws_directory.Cells(i, 2), Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
        i = i + 1
    Next ws
End Sub

cmo9020

放到Thiswokbook


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    SheetNames
End Sub



模

Sub SheetNames()
    Dim ws As Worksheet
    Dim i As Integer
    Dim ws_directory As Worksheet
    Set ws_directory = ThisWorkbook.Sheets("目录")

    i = 2

    For Each ws In ThisWorkbook.Sheets
        ws_directory.Cells(i, 2).Value = ws.Name
        ws_directory.Cells(i, 2).Hyperlinks.Add Anchor:=ws_directory.Cells(i, 2), Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
        i = i + 1
    Next ws
End Sub