EXCEL目录宏(自动生成工作薄目录)

查阅了相关资料现将方法分享给大家：打开你要创建，的#格#按Alt+F11在谈出的对话框中点插入选择模块然后把下面的代码复制到模块中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复制完成后按F5运行即可，如果后期对表名做了修改，只需要重新F5运行刷新下目录即可。