OzGrid

Create a Sheet Index of Excel Worksheets

< Back to Search results

 Category: [Excel]  Demo Available 

 

Create a Sheet Index of Excel Worksheets

 

Index of all Microsoft Excel Worksheets

 

Got any Excel Questions? Free Excel Help

Creating a Index Worksheet

When working with Excel, many users end up with a lot of different Worksheets which then makes navigation cumbersome. This can be over come in a few ways, with the best being keeping the number of Worksheets in an Excel Workbook to a minimum. This not only helps with navigation, but also means a far more efficient spreadsheet design . Ideally one should store all raw data on one Worksheet, in a classic table format (headings across the top and data laid out beneath) and then use another Worksheet to extract, report, chart and filter out needed data. However, sometimes this is just not possible for many reasons which is why we will see how we can work-around by creating an index sheet.

Before we jump into that, let's first ensure you are aware of Excel's built in pop-up sheet index. To see this simply right click on the sheet scroll tabs to the left of the name tabs. By doing this you will see a pop-up menu like shown below;
Excel pop-up sheet menu

Upon clicking "More sheets..." you will see all Worksheets listed in a nice scrollable ListBox as shown below

Worksheet Index

If this is still not what you need we can create a Worksheet Index. First add a new sheet to the Workbook and call it "Index" (optional). Next right click on the sheet name tab of the "Index" sheet and select "View Code". In here you should paste the code as shown below

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long

l = 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
            l = l + 1
                With wSheet
                    .Range("A1").Name = "Start_" & wSheet.Index
                    .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
                    SubAddress:="Index", TextToDisplay:="Back to Index"
                End With

                Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
    Next wSheet

End Sub

Now click the top right X to get back to Excel proper and then Save. Now, each time you activate the Index sheet the old list will be cleared and a new one will be added. This ensures the index shown is always up-to date should you add or delete Worksheet. You will also note that a range based on the Worksheet name is added to each Worksheet in range A1. If this clashes with existing names, simply change;

 .Range("A1").Name = "Start " & wSheet.Index AND Anchor:=.Range("A1")

To

 .Range("B1").Name = "Start " & wSheet.Index AND Anchor:=.Range("B1")

Or any other cell. You will also note that on each Worksheet is a "Back To Index" hyperlink

See also:

Index to Excel VBA Code
Select Case Statement in Excel VBA
Send Emails From Excel

 

See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


Gallery



stars (0 Reviews)