Over the past few weeks I’ve been working in an Excel file that contains many worksheets. Switching back and forth between worksheets using the scroll buttons has been nothing less than annoying. Then I remembered the hand dandy feature of Excel that lets you see a list of all worksheets. All you need to do is right one of the scroll buttons and a list of worksheets appear. Selecting one of the worksheets takes you right to that tab.
Now not everyone who opens the spreadsheet knows about this feature. When you share an Excel file with others, it would be nice if it could include a table of contents or index of worksheets. It is possible to do in Excel but requires a few steps.
- Open your Excel file.
- Insert a new worksheet at the beginning of the workbook. To do this, right click the first worksheet and click Insert. Select worksheet and click OK.
- Right click the new worksheet and click Rename. Type Index as the name and press Enter.
- Click the Tools menu, point to Macros, and click Visual Basic Editor.
- Click the Index worksheet and from the View menu click Code.
- Paste the following code in:
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1
.Cells(1, 1) = “INDEX”
.Cells(1, 1).Name = “Index”
For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
l = l + 1
.Range(“A1”).Name = “Start_” & wSheet.Index
.Hyperlinks.Add Anchor:=.Range(“A1″), Address:=””, _
SubAddress:=”Index”, TextToDisplay:=”Back to Index”
Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:=””, _
SubAddress:=”Start_” & wSheet.Index, TextToDisplay:=wSheet.Name
Click the top right X to get back to Excel, and then save the Excel workbook.
When you reopen the workbook, the index should be listed with links to all the worksheets.