分享一个群友今天咨询的问题
Lao少侠
Excel函数|VBA交流②群
请教:Excel如何自动生成有超链接的Sheet目录?
如下图中所示的工作簿中,有很多个Sheet。目的是把所有的Sheet在目录表中制作成超链接的形式,点击跳转。
解决方案:录制宏+循环
下面的东西可能很啰嗦,针对小白。需要代码的可直接跳到最后。
✔这里我先不给出答案,我给出我来做这个思路:
我想到要做目录,首先要对所有的Sheet进行循环遍历,获取Sheet的名字,然后在目录表中列出来每个Sheet的名字,并且添加超链接。
■首先放一个框架,我要循环每个Sheet,获取sheet的名字:
Sub 循环每个sheet并获取名字()
For Each sht In Worksheets
MsgBox sht.Name
Next
End Sub
但是我们的目录本身这个sheet不参与写入目录,所以需要嵌套一个If判断语句,当sheet的名字不是目录的时候,我们才获取名字。
Sub 循环每个sheet并获取名字()
For Each sht In Worksheets
If sht.Name <> "目录" Then
MsgBox sht.Name
Else
End If
Next
End Sub
名字获取到了,我们想把它按顺序写入到目录这个表的A列,我们添加一个变量i,用于记录表格个数,同时也是为了记录已使用的行:
Sub 循环每个sheet并获取名字()
For Each sht In Worksheets
If sht.Name <> "目录" Then
i = i + 1
Cells(i, 1) = sht.Name
Else
End If
Next
End Sub
运行完上述代码后,出现下面的结果(我们已经成功一半了):
■下面我们就要对A列的名字添加超链接了。这个记不下来,只能录制宏来解决。
我们以添加【1日】这个表的超链接为例,录制宏我们得到了一堆代码:
Sub 宏1()
Range("A1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'1日'!A1", TextToDisplay:="1日"
End Sub
通过查看帮助,我知道了添加超链接的具体参数含义:
实际表名.Hyperlinks.Add 创建位置,地址,副地址,屏幕提示,显示文本
修修改改,套入循环框架后,最终我们的代码如下:
Sub 循环每个sheet并获取名字()
For Each sht In Worksheets
If sht.Name <> "目录" Then
i = i + 1
Cells(i, 1) = sht.Name
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="", SubAddress:=sht.Name & "!A1"
Else
End If
Next
End Sub
然后小伙伴又说了:
Lao少侠
Excel函数|VBA交流②群
请教:想在每个分表中添加【返回目录】的超链接
其实只需要加一句代码即可:
Sub 循环每个sheet并获取名字()
For Each sht In Worksheets
If sht.Name <> "目录" Then
i = i + 1
Cells(i, 1) = sht.Name
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="", SubAddress:=sht.Name & "!A1"
sht.Hyperlinks.Add Anchor:=sht.Cells(1, 1), Address:="", SubAddress:="目录!A1", TextToDisplay:="返回目录"
Else
End If
Next
End Sub
最终效果如下:
原创不易,如果你觉得不错,希望你能帮忙转发或者再看~
= 推荐阅读 =
【经验】快速学习VBA
【新手必备】VBA练手专题
Excel提取Word简历数据.xls
代码存储美化工具测评-【VBE2019】
代码合集,各取所需【操作图片】