24. VBA Tips - Create An Index Page

Nick's picture

This tip shows you how to create an Index page with hyperlinks to each sheet:

Here's a screen shot of what we will produce:



Here's the code we use:





  1. To run this, download the example file, and press the button
  2. The button runs the "CreateIndex" macro
  3. Application.DisplayAlerts = False  is a command that turns off alerts.
    • The reason we have this is so that if we rerun the code and delete the existing Index Sheet, Excel doesn't popup message boxes.
  4.     On Error Resume Next
        On Error GoTo 0
    • This part of the code deletes the Index sheet if it already exists
  5. We then add a new index sheet
  6. Now, we loop through each sheet, and add a hyperlink to the first cell on the sheet thus creating our index page. 

Download sheet to practise how to Create An Index Page in Excel

Training Video on how to Create An Index Page in Excel:


create-an-index-page.xls55 KB

Freeze index tab


i have the macro code up and running.
i hid the Code page tab,
i hid the code page index listing by hiding the row
can i freeze the index page tab to always appear on the left?
i did know the right click short cut but i wish to have my customer see all the pages in one view. maybe even print the page out as a register.

Donald Sunshine Crane Repairs.

Nick's picture

you can't freeze the position

you can't freeze the position of a sheet... you can write some VBA for it though

Links not valid to sheets with spaces in the names?

I just discovered your site this morning and it's awesome.

In using this macro, I've noticed it does not create functional links when the sheet names include spaces or - (e.g. "Report Comments" or "EF-1")

In making a Table of Contents for a workbook report, quite often there is a cover page 1st, so really the created should be created as the second sheet.

Nick's picture

2 solutions

tks for ur kind comments... 2 solutions for you here are ur solutions: replace this line:
            ActiveSheet.Hyperlinks.Add Anchor:=Cells(iRow, 1) _
             , Address:="", SubAddress:= _
             "'" & theSheet.Name & "'" & "!A1", TextToDisplay:=theSheet.Name
and add this at the end:
    Sheets("Index").Move After:=Sheets(1)

Wish you'd updated the code...

I just debugged the missing ' issue on shaeet names with spaces and came back to help out others only to find the problem had already been solved. Note to self... read the comments before debugging.

Awesome macro, Thank you.

Application.DisplayAlerts = False

Thank you Nick for the videos, they are great! In a previous video about application.displayalerts you said that we shouldn´t forget to turn it on again at the end. Is there any reason why you didn´t put "Application.DisplayAlerts = True" at the end on this one. Hope you post many more videos.

Create An Index Page

Dear Sir

I have created the index page but now i want when i move to sheet1 with index i want again to go back to index pls help me on this. And is there a possibility to create index other than col & row 1 not in the middle of worksheet.


not entirely sure what you want here, but another very good way to navigate around sheets is to right click on the bottom left of Excel.. the book arrows above "Ready"
This shows all sheets in a popup.

Nick's picture

Application.DisplayAlerts = False

Good spot.

I should at least follow my own best practises.