Here is some vba code that I have written that creates an "Index Sheet" at the beginning of the workbook.
You could update the code to delete the Index Sheet before it runs each time that way you don't have to manually do it?
Option Explicit
Public Sub SheetNames()
'Macro created for Carlyle Coinvest
'Macro by Matt Nuttall
Dim i As Variant
Dim MainWks As Worksheet
Dim r As Long
Dim Wks As Worksheet
Dim x As Long
Dim F As Variant
Application.ScreenUpdating = False
Worksheets(1).Activate
Worksheets.Add().Name = "Index Sheet"
Worksheets("Index Sheet").Activate
Set MainWks = Worksheets("Index Sheet")
For Each Wks In Worksheets
If Wks.Name <> MainWks.Name Then
r = r + 1
With MainWks
.Hyperlinks.Add Anchor:=.Cells(r, "A"), _
Address:="", _
SubAddress:="'" & Wks.Name & "'!A1", _
TextToDisplay:=Wks.Name
End With
End If
Next Wks
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Value = 2
Range("A2").Value = 3
With ActiveSheet
x = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
Range("A1:A2").Select
F = Range(Cells(1, ActiveCell.Column), Cells(x, ActiveCell.Column)).Address(False, False)
With Selection
.AutoFill Destination:=Range(F)
End With
Range("A1").CurrentRegion.Copy Range("F1")
Columns("A:B").Select
ActiveWorkbook.Worksheets("Index Sheet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Index Sheet").Sort.SortFields.Add Key:=Range( _
"B1:B200"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Index Sheet").Sort
.SetRange Range("A1:B200")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").EntireRow.Insert Shift:=xlDown
Range("A1").EntireRow.Insert Shift:=xlDown
Range("A1").EntireRow.Insert Shift:=xlDown
Range("A1").EntireRow.Insert Shift:=xlDown
Range("A1").EntireRow.Insert Shift:=xlDown
Range("A4").Value = "Alphabetized"
Range("A5").Value = "Tab Number"
Range("B5").Value = "Sheet Name"
Range("A4:B4").MergeCells = True
Range("F4").Value = "Workbook Order"
Range("F5").Value = "Tab Number"
Range("G5").Value = "Sheet Name"
Range("F4:G4").MergeCells = True
Range("A4:B4,F4:G4").Font.Bold = True
Range("A4:B4,F4:G4").HorizontalAlignment = xlCenter
Range("A5:B5,F5:G5").Font.Underline = xlUnderlineStyleSingle
Range("A1").Select
Range("A1").Value = "Table of Contents"
Range("A1:G2").MergeCells = True
Range("A1:G2").HorizontalAlignment = xlCenter
With Selection.Font
.Name = "Arial"
.Size = 20
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Columns("A:G").EntireColumn.AutoFit
Range("A1").Select
Application.ScreenUpdating = True
End Sub
Good luck.
