excel索引 excel索引目录 _知识经验

文章插图
大家好,小跳来为大家解答以上的问题。excel索引目录，excel索引这个很多人还不知道,现在让我们一起来看看吧！
【excel索引 excel索引目录】1、先可新建一个工作表《索引》。
2、插入名称-定义。
3、运用transpose(get,wordbook(1)提取足够多的工作表名再用超链接公式链接用宏就比较简单了我给你个宏的例子根据你表格的情况还要做部分修改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 = 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 = 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 = True.Interior.ColorIndex = 34End WithApplication.StatusBar = FalseApplication.ScreenUpdating = TrueTuichu:End Sub 。
本文到此分享完毕，希望对大家有所帮助。