独角兽企业重金招聘Python工程师标准>>>
用office excel打开excel文件
Alt+F11->插入->模块->把下面的代码拷贝到模块->F5 添加页签后重复此操作,目录自动生成
Submulu()OnErrorGoToTuichuDimiAsIntegerDimShtCountAsIntegerDimSelectionCellAsRangeShtCount=Worksheets.CountIfShtCount=0OrShtCount=1ThenExitSubApplication.ScreenUpdating=FalseFori=1ToShtCountIfSheets(i).Name="目录"ThenSheets("目录").MoveBefore:=Sheets(1)EndIfNextiIfSheets(1).Name<>"目录"ThenShtCount=ShtCount+1Sheets(1).SelectSheets.AddSheets(1).Name="目录"EndIfSheets("目录").SelectColumns("B:B").DeleteShift:=xlToLeftApplication.StatusBar="正在生成目录请等待!"Fori=2ToShtCountActiveSheet.Hyperlinks.AddAnchor:=Worksheets("目录").Cells(i,2),Address:="",SubAddress:="'"&Sheets(i).Name&"'!R1C1",TextToDisplay:=Sheets(i).NameNextSheets("目录").SelectColumns("B:B").AutoFitCells(1,2)="目录"SetSelectionCell=Worksheets("目录").Range("B1")WithSelectionCell.HorizontalAlignment=xlDistributed.VerticalAlignment=xlCenter.AddIndent=True.Font.Bold=True.Interior.ColorIndex=34EndWithApplication.StatusBar=FalseApplication.ScreenUpdating=TrueTuichu:EndSub