《excel页签自动生成目录.doc》由会员分享,可在线阅读,更多相关《excel页签自动生成目录.doc(3页珍藏版)》请在三一文库上搜索。
1、Alt+F11 插入 模块 把下面的代码拷贝到模块中1,首先在 excel 文件的最前面追加一个 sheet ,命名为“目录”2,按 Alt+F11 在谈出的对话框中点插入选择模块然后把下面的代码复制到模块 中Sub mulu()On Error GoTo TuichuDim i As IntegerDim ShtCount As IntegerDim SelectionCell As RangeShtCount = Worksheets.CountIf ShtCount = 0 Or ShtCount = 1 Then Exit SubApplication.ScreenUpdating =
2、 FalseFor i = 1 To ShtCountIf Sheets(i).Name = 目录 ThenSheets(目录 ).Move Before:=Sheets(1)End IfNext iIf Sheets(1).Name 目录 ThenShtCount = ShtCount + 1Sheets(1).SelectSheets.AddSheets(1).Name = 目录 End IfSheets( 目录 ).SelectColumns(B:B).Delete Shift:=xlToLeftApplication.StatusBar = 正在生成目录 , 请等待! For i =
3、2 To ShtCountActiveSheet.Hyperlinks.Add Anchor:=Worksheets(目录 ).Cells(i,2), Address:=, SubAddress:= _ & Sheets(i).Name & !R1C1, TextToDisplay:=Sheets(i).NameNextSheets( 目录 ).SelectColumns(B:B).AutoFitCells(1, 2) = 目录 Set SelectionCell = Worksheets(目录 ).Range(B1)With SelectionCell .HorizontalAlignment = xlDistributed .VerticalAlignment = xlCenter.AddIndent = True.Font.Bold = TrueF5 运行.Interior.ColorIndex = 34 End With Application.StatusBar = False Application.ScreenUpdating = True Tuichu: End Sub3,复制完成后按 F5 运行即可,如果后期对表名做了修改,只需要重新 刷新下目录即可。