Excel如何得到当前工作表目录? VBA代码一键生成目录

2022年2月19日09:59:54已关闭评论 769 views

微信公众号【EXCEL880】 QQ群【341401932】
课程咨询加我微信EXCEL880B 试学网址http://v.excel880.com

Excel如何得到当前工作表目录?
VBA代码几句话就行了

打开VBA 窗口插入模块粘贴下面代码运行即可

Sub 创建目录Q80871835()
'加微信EXCEL880B领取免费试学 郑广学作品 EXCEL880.COM
    Dim sht As Worksheet
    On Error Resume Next '忽略错误继续执行VBA代码,避免出现错误消息
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False '关闭删除目录表时的提示
    Sheets("目录").Delete
    Application.DisplayAlerts = True
    Set sht = Sheets.Add(before:=Sheets(1))
    sht.name = "目录"
    sht.[A1] = "序号"
    sht.[B1] = "目录"
    For i = 2 To Sheets.Count
        sht.Cells(i, 1) = i - 1
        sht.Cells(i, 2) = Sheets(i).name
        sht.Hyperlinks.Add Anchor:=sht.Cells(i, 2), Address:="", SubAddress:= _
            "'" & Sheets(i).name & "'!A1", TextToDisplay:=Sheets(i).name '主表添加超链接
        Sheets(i).Hyperlinks.Add Anchor:=Sheets(i).Range("E1"), Address:="", SubAddress:= _
            "目录!B" & i, TextToDisplay:="返回目录" '子表添加返回超链接
    Next
    Application.ScreenUpdating = True
    MsgBox "目录创建完成"
End Sub