Create An Index Of Your Excel Worksheets (Tips)

Quoted from Create An Index Of Your Excel Worksheets (Tips)
1. Open your Excel workbook.
2. 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.
3. Right click the new worksheet and click Rename.

Type in Index and press Enter.

4. Click the Tools menu, point to Macros, and click Visual Basic Editor.
5. Click Sheet1 (Index) and from the View menu click Code.
6. Paste the following code in:

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim M As Long
M = 1
With Me

.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
M = M + 1
With wSheet
.Range("H1").Name = "Start" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("H1"), Address:="", SubAddress:="Index", TextToDisplay:="Back to Index"
End With
Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1), Address:="", SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
End Sub

7. Click the Excel icon on the toolbar.

8. Save the Excel workbook and close it.
When you reopen the workbook, the index should be listed with links to all the worksheets.

Each worksheet will also contain a link that will return you to the Index worksheet.



Related Posts Plugin for WordPress, Blogger...