自动生成工作表目录（批量生成超链接）

作者: 许泽博　发布: 2016/5/7　分类: 电脑　阅读: 次　查看评论

 背景：一个工作簿，N个工作表，需要按照每个工作表的名称自动生成目录，并有超链接。

实现方法非常之简单，一公式即定。
实现步骤
一、定义名称
CTRL+F3 - 新建名称
名称：AllShs
范围：工作簿
引用位置：=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),)&T(NOW())
GET.WORKBOOK(1)是宏表函数，以[Book1.xls]Sheet1的形式返回工作簿中所有工作表名的水平数组；
REPLACE(GET.WORKBOOK(1),1,FIND…是为了去掉前面的工作簿名，只保留工作表名；
T(NOW())="" 不影响公式结果，而NOW是易失性函数，可以使工作表改名或增加、删除、移动工作表时，公式能自动重新计算。
二、输入公式
=IF(ROW()>COUNTA(AllShs),"",HYPERLINK("#"&INDEX(AllShs,ROW())&"!A1","GoTo "&INDEX(AllShs,ROW())&"!A1"))
在任意单元格输入以上公式，下拉。
公式的中文解析 - 判断行号大于工作表数量则置空，不然，则运用HYPERLINK函数为工作表建立超链接。公式中AllShs为定义的名称，也是关建所在。
三、宏代码（非原创）

Sub mulu()
On Error GoTo Tuichu
Dim i As Integer
Dim shtcount As Integer
Dim SelectionCell As Range
shtcount = Worksheets.Count
If shtcount = 0 Or shtcount = 1 Then Exit Sub
Application.ScreenUpdating = False
For i = 1 To shtcount
If Sheets(i).Name = "目录" Then
Sheets("目录").Move before:=Sheets(1)
End If
Next i
If Sheets(1).Name <> "目录" Then
shtcount = shtcount + 1
Sheets(1).Select
Sheets.Add
Sheets(1).Name = "目录"
End If
Sheets("目录").Select
Columns("B:B").Delete Shift:=xlToLeft
Application.StatusBar = "正在生成目录…………请等待！"
For i = 2 To shtcount
ActiveSheet.Hyperlinks.Add Anchor:=Worksheets("目录").Cells(i, 2), Address:="", SubAddress:= _
"'" & Sheets(i).Name & "'!R1C1", TextToDisplay:=Sheets(i).Name
Next
Sheets("目录").Select
Columns("B:B").AutoFit
Cells(1, 2) = "目录"
Set SelectionCell = Worksheets("目录").Range("B1")
With SelectionCell
.HorizontalAlignment = xlDistributed
.VerticalAlignment = xlCenter
.AddIndent = True
.Font.Bold = True
.Interior.ColorIndex = 34
End With
Application.StatusBar = False
Application.ScreenUpdating = True
Tuichu:
End Sub
注意：TextToDisplay:=Sheets(i).Name可以更改为TextToDisplay:=”go to  “Sheets(i).Name
显示为：go to 工作表标签

文章来源EXCELHOME

« 上一篇下一篇 » 原创文章，转载请注明出处！标签: 电脑  EXCEL  

评论列表:

说两句吧:

必填

选填

选填

必填，不填不让过哦，嘻嘻。

记住我,下次回复时不用重新输入个人信息

站内公告
欢迎进入我的博客，文章主要来源于网络，若有涉及版权问题，请您及时联系我，也希望我的博客能对您有所帮助！
控制面板
您好,欢迎到访网站!
  [查看权限]
用户注册
纪念日倒计时



网站分类
友情链接
Tags列表