E-Mail:

Create An Index Of Your Excel Worksheets

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:

  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
    .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

  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.

    [tags]excel,workbook,worksheet index,sheet1,diana huggins[/tags]

4 Comments

I followed all of the steps but it did not work :(

I got a Compile error: Syntax error at:

If wSheet.Name Me.Name Then

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

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.NameMe.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

What Do You Think?

 

Want to Start a Blog Here for Free?

Are you an expert in one subject or another? If your goal is to help others and dispense hard-earned information back to the community, stake a claim on your very own Lockergnome blog today! You can write about anything - no matter the topic. Sign-up to start blogging!

Favorite - Oct 6, 2008

wowApic

Deals - Oct 6, 2008

Looking To Buy? Looking To Sell? Try SecondSpin.com

Diana's Tips - Oct 2, 2008

Change The Page Orientation In Word 2003

Feedback, Tips - Oct 1, 2008

Top 5 Reasons To Buy A Custom Built Computer Over A Pre-Built Machine

87 queries / 0.696 seconds.