Create Worksheet Index in your workbook

If you want to create a worksheet index which will have all the sheet names with hyperlinks . If you click on the cell it should activate the sheet whose name is displayed in that cell. Snapshot Below -

The macro will add a new worksheet with sheet name "Index" at the beginning and will print all worksheet names and add hypelinks in column A.

Here is the code-

Sub Create_sheet_index()
Dim I As Long

' http://cloford.com/resources/colours/500col.htm FOR RGB COLOR LISTING
' create a new table of content
Sheets(1).Select
Sheets.Add
' give a name to your index sheet
ActiveSheet.Name = "Index"
ActiveWindow.DisplayGridlines = False
With Range("a1")
.Value = "Index"
.Font.Bold = True
.Font.Size = 20
.HorizontalAlignment = xlCenter
.Interior.Color = RGB(152, 245, 255)
End With
Columns("A:A").ColumnWidth = 65
' ADDING SHEET NAMES AND HYPERLINKS

For I = 2 To Sheets.Count
Range("a" & I).Value = Sheets(I).Name
Range("a" & I).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" &Range("a" & I).Value & "'!A1", TextToDisplay:=Range("a" & I).Value

'APPLY BACKGROUND COLOR TO SHEET NAMES
With Range("a" & I)
.Font.Size = 12
.HorizontalAlignment = xlCenter
.Interior.Color = RGB(191, 239, 255)
End With

' APPLY BORDERS
With ActiveSheet.Range("A" & I - 1 & ":A" & I)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
End With

Next I
Cells(1, 1).Select

End Sub


Excel Macro File - http://www.filefactory.com/file/c5d2a27/n/SHEET_NAMES.xlsm