Create An Index Of Your Excel Worksheets
- 9
- Add a Comment
- No Related Post
If your Excel workbook contains numerous worksheets, you can end up spending a good deal of time searching through them to find the one you are looking for. Why not optimize your time and create an index sheet that has links to all the worksheets. This way when you are looking for a specific worksheet, you can reference the index sheet instead of clicking through all the worksheets.
You can create an index by following the procedure described below:
- Open your Excel workbook.
- 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 in Index and press Enter.
- Click the Tools menu, point to Macros, and click Visual Basic Editor.
- Click Sheet1 (Index) and from the View menu click Code.
- Paste the following code in:
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim M As Long
M = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = “INDEX”
.Cells(1, 1).Name = “Index”
End WithFor 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 - Click the Excel icon on the toolbar.
- 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.
[tags]excel,workbook,worksheet index,sheet1,diana huggins[/tags]
9 Comments
Sabine
April 4th, 2008
at 12:41pm
I followed all of the steps but it did not work
Jerry
May 21st, 2008
at 4:40pm
I got a Compile error: Syntax error at:
If wSheet.Name Me.Name Then
maoumaou
June 6th, 2008
at 3:11am
some mistake in the code
please use
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim M As Long
M = 1
With Me
.Columns(1).ClearContents
.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
Joe
June 23rd, 2008
at 3:25am
I followed the procedure and used this code and it worked for me:
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim M As Long
M = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = “INDEX”
.Cells(1, 1).Name = “Index”
End With
For Each wSheet In Worksheets
If StrComp wSheet.Name, Me.Name) Then
M = M + 1
With wSheet
.Range(”A2″).Name = “Start” & wSheet.Index
.Hyperlinks.Add Anchor:=.Range(”A2″), 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
Joe
November 7th, 2008
at 6:27pm
If StrComp wSheet.Name, Me.Name) Then
Joe
November 7th, 2008
at 6:30pm
There should be an opening parenthesis before wSheet.Name, to match the closing parenthesis after Me.Name. For some reason it doesn’t show in either of the code I published above.
Eric Metzger
February 24th, 2009
at 9:34am
I finally got the code to work after correcting some of the code spaces and proper symbols. Now I receive the Message “Reference is not valid”. Can anyone tell me where I went wrong? Here is the code:
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim M As Long
M = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = “Index”
.Cells(1, 1).Name = “Index”
End With
For Each wSheet In Worksheets
If StrComp wSheet.Name, Me.Name) Then
M = M + 1
With wSheet
.Range(”A2″).Name = “Start” & wSheet.Index
.Hyperlinks.Add Anchor:=.Range(”A2″), 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
Clint
October 4th, 2009
at 8:57am
Helped thanks. Did notice that using quote marks ” using copy and paste produces errors, may be the ASCII codes that differ so just manually over type all of them, The single parenthesis in the If StrComp wSheet.Name, Me.Name) Then
is still missing after StrComp . Replace any ? after copy and pasting with ” and it works great
Pieter
October 8th, 2009
at 6:37am
Everything worked out great after replacing ASCII codes with typed quote marks “.