Excel利用VBA代码一键生成各个工作表超链接目录

 

Sub 生成目录()

    Dim wsht As Worksheet, k%, wshtname$

    Columns(1).ClearContents '清空A列数据,这样就可以随着工作表的变化，生成不同的工作表目录

    Range("a1") = "超链接目录" '第一个单元格写入字符串

    k = 1 '赋予参数初值

    For Each wsht In Worksheets '找到当前工作簿的每个工作表

        wshtname = wsht.Name

        If wshtname <> ActiveSheet.Name Then

          k = k + 1

          ActiveSheet.Hyperlinks.Add anchor:=Cells(k, 1), Address:="", SubAddress:="'" & wshtname & "'!a1", TextToDisplay:=wshtname

        End If

    Next

End Sub