|
建立工作表目录可以用如下代码- Sub 建立工作表目录()
- On Error Resume Next
- Application.ScreenUpdating = False
- '检测是否已经“目录”
- For i = 1 To Worksheets.count
- If Worksheets(i).Name = "目录" Then
- Exit For
- End If
- Next
- If i > Worksheets.count Then
- Sheets.Add '如果不存在“目录”则添加新表
- ActiveSheet.Name = "目录" '命名工作表
- End If
- Sheets("目录").Move Before:=Sheets(1) '称到最前
- Sheets("目录").Select
- Range("D:E").Clear
- Range("D:E").NumberFormatLocal = "@" '将D、E列清除并设置格式为文本型
- Range("D1:E1") = Array("编号", "目录") '标题
- For i = 2 To Worksheets.count '在工作表间循环
- Cells(i, 4).Value = i - 1 '编号
- Cells(i, 5).Value = Worksheets(i).Name '工作表表
- '添加链接
- ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 5), Address:="", SubAddress:="'" & Worksheets(i).Name & "'" & "!A1", TextToDisplay:=Worksheets(i).Name, ScreenTip:="单击打开:" & Cells(i, 5)
- Next
- '设置上下、左右都居中对齐
- Columns("D:D").HorizontalAlignment = xlCenter
- Columns("E:E").HorizontalAlignment = xlLeft
- Cells.Interior.ColorIndex = 1 '黑色背景
- Cells.Font.ColorIndex = 2 '白色字体
- Range("d2").Select '选择D
- ActiveWindow.FreezePanes = True '锁定窗格
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|