24. VBA Tips - Create An Index Page
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:
Explanation:
- To run this, download the example file, and press the button
- The button runs the "CreateIndex" macro
- 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.
- On Error Resume Next
Sheets("Index").Delete
On Error GoTo 0- This part of the code deletes the Index sheet if it already exists
- We then add a new index sheet
- 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:
| Attachment | Size |
|---|---|
| create-an-index-page.xls | 55 KB |
»
- Nick's blog
- Login or register to post comments
- 41377 reads
Freeze index tab
Hello
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.
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.
2 solutions
ActiveSheet.Hyperlinks.Add Anchor:=Cells(iRow, 1) _ , Address:="", SubAddress:= _ "'" & theSheet.Name & "'" & "!A1", TextToDisplay:=theSheet.NameSheets("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.
index
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.
Application.DisplayAlerts = False
Good spot.
I should at least follow my own best practises.