# Create a Sheet Index of Excel Worksheets

## Index of all Microsoft Excel Worksheets

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only \$145.00. \$59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

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;

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
End With

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

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over \$64.00. ALL purchases totaling over \$150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.