Here's some slick code by Zack Barresse that will build a table of contents:
<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> CreateTOC()<br> <SPAN style="color:#007F00">' Code by Zack Baresse</SPAN><br> <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br> MsgBox "You must have a workbook open first!", vbInformation, "No Open Book"<br> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <br> <SPAN style="color:#00007F">With</SPAN> Application<br> .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br> .DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br> <br> <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet, _<br> ct <SPAN style="color:#00007F">As</SPAN> Chart, _<br> shtName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _<br> nrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br> tmpCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br> numCharts <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br> <br> nrow = 3<br> i = 1<br> numCharts = ActiveWorkbook.Charts.Count<br> <br> <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> hasSheet<br> Sheets("TOC").Activate<br> <SPAN style="color:#00007F">If</SPAN> MsgBox("You already have a Table of Contents page. Would you like to overwrite it?", _<br> vbYesNo + vbQuestion, "Replace TOC page?") = vbYes <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> createNew<br> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br>hasSheet:<br> Sheets.Add Before:=Sheets(1)<br> <SPAN style="color:#00007F">GoTo</SPAN> hasNew<br><br>createNew:<br> Sheets("TOC").Delete<br> <SPAN style="color:#00007F">GoTo</SPAN> hasSheet<br><br>hasNew:<br> tmpCount = ActiveWorkbook.Charts.Count<br> <SPAN style="color:#00007F">If</SPAN> tmpCount > 0 <SPAN style="color:#00007F">Then</SPAN> tmpCount = 1<br> ActiveSheet.Name = "TOC"<br> <br> <SPAN style="color:#00007F">With</SPAN> Sheets("TOC")<br> .Cells.Interior.Color<SPAN style="color:#00007F">In</SPAN>dex = 4<br> <SPAN style="color:#00007F">With</SPAN> .Range("B2")<br> .Value = "Table of Contents"<br> .Font.Bold = <SPAN style="color:#00007F">True</SPAN><br> .Font.Name = "Tahoma"<br> .Font.Size = "24"<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> <br> <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br> nrow = nrow + 1<br> <SPAN style="color:#00007F">With</SPAN> ws<br> shtName = ws.Name<br> <SPAN style="color:#00007F">With</SPAN> Sheets("TOC")<br> .Range("B" & nrow).Value = nrow - 3<br> .Range("C" & nrow).Hyperlinks.Add _<br> Anchor:=Sheets("TOC").Range("C" & nrow), Address:="#'" & _<br> shtName & "'!A1", TextToDisplay:=shtName<br> .Range("C" & nrow).HorizontalAlignment = xlLeft<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> ws<br> <br> <SPAN style="color:#00007F">If</SPAN> numCharts <> 0 <SPAN style="color:#00007F">Then</SPAN><br> <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ct In ActiveWorkbook.Charts<br> nrow = nrow + 1<br> shtName = ct.Name<br> <SPAN style="color:#00007F">With</SPAN> Sheets("TOC")<br> .Range("B" & nrow).Value = nrow - 3<br> .Range("C" & nrow).Value = shtName<br> .Range("C" & nrow).HorizontalAlignment = xlLeft<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> ct<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <br> <SPAN style="color:#00007F">With</SPAN> Sheets("TOC")<br> <SPAN style="color:#00007F">With</SPAN> .Range("B2:G2")<br> .MergeCells = <SPAN style="color:#00007F">True</SPAN><br> .HorizontalAlignment = xlLeft<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> <br> <SPAN style="color:#00007F">With</SPAN> .Range("C:C")<br> .EntireColumn.AutoFit<br> .Activate<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> .Range("B4").Select<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> <br> .DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br> .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> <br> MsgBox "Done!" & vbNewLine & vbNewLine & "Please note: " & _<br> "Charts are listed after regular " & vbCrLf & _<br> "worksheets and will not have hyperlinks.", vbInformation, "Complete!"<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
HTH,
