excel怎么获取当前工作表(Excel如何得到当前工作表目录?)

Excel如何得到当前工作表目录?

VBA代码几句话就行了

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

更多VBA实战案例 请点击下方专栏学习#excel##办公##vba#

'-----------------

Sub 创建目录()

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