Excel VBA代码实现 动态目录与返回键

VBA学习笔记备忘1

Sub 目录()

Dim sht As Worksheet
Dim mulu As Worksheet
Dim k

On Error Resume Next
Application.DisplayAlerts = False
Sheets("目录").Delete '若已有目录，删除原来的目录
Application.DisplayAlerts = True

Set mulu = Worksheets.Add(Sheets(1))
mulu.Name = "目录"

For Each sht In Worksheets
    If sht.Name <> "目录" Then
        k = k + 1
        'mulu.Range("a" & k) = "=HYPERLINK(表一!A1,"表一")"
        mulu.Range("a" & k) = "=HYPERLINK(""# " & sht.Name & "!A1"",""" & sht.Name & """)"
        sht.[a1] = "=HYPERLINK(""#目录!A1"",""返回目录"")"
        '注意函数表达式中带有引号的，此时要全部翻倍，常量修改为变量时，使用" & sht.Name & "的格式
    End If
Next

End Sub

代码参考自B站UP：@我是知了zZ