Macro to create index sheet

Topic

New Reply

I would like a macro to create an index sheet that updates when new sheets are added or deleted

Reply | Quote

Replies

Howard,

Here’s one possible solution:

Code:

Option Explicit

Private Sub Worksheet_Activate()

 Dim sht      As Worksheet
 Dim lCurRow  As Long
 
 lCurRow = 2
 
 For Each sht In ActiveWorkbook.Sheets
 
    If sht.Name  "Index" Then
      Cells(lCurRow, 1).Select
      ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        sht.Name & "!A1", TextToDisplay:=sht.Name
    lCurRow = lCurRow + 1
    End If
    
 Next sht
 
 '*** Clear out any deleted sheets ***
 Cells(lCurRow, 1).Select
 Range(Selection, Selection.End(xlDown)).Delete Shift:=xlUp
 
End Sub

35367-Excel-index
Note that the code goes in the Index Sheet module. See attached sample file. The index is automatically recreated every time the Index sheet is selected.
35368-VBA-Excel-Create-Index-sheet
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

You recently helped me with code to create an index page with Hyperlinks. When I run the macro and click on the hyperlink, I get a message “Reference is not valid”

I have attached my workbook. Please check & advise

Howard

Reply | Quote

Howard,

Here’s one possible solution:

Code:

Option Explicit

Private Sub Worksheet_Activate()

 Dim sht      As Worksheet
 Dim lCurRow  As Long
 
 lCurRow = 2
 
 For Each sht In ActiveWorkbook.Sheets
 
    If sht.Name  "Index" Then
      Cells(lCurRow, 1).Select
      ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        sht.Name & "!A1", TextToDisplay:=sht.Name
    lCurRow = lCurRow + 1
    End If
    
 Next sht
 
 '*** Clear out any deleted sheets ***
 Cells(lCurRow, 1).Select
 Range(Selection, Selection.End(xlDown)).Delete Shift:=xlUp
 
End Sub

35367-Excel-index
Note that the code goes in the Index Sheet module. See attached sample file. The index is automatically recreated every time the Index sheet is selected.
35368-VBA-Excel-Create-Index-sheet
HTH :cheers:

Works great, just note that sheet names must not have spaces or dashes. Apparently, these special characters break the code.

Reply | Quote

Hi RG

Thanks for the help

Regards

Howard

Reply | Quote

Sweet RG!

Assuming the Index sheet is the first sheet, here is another way to index the sheets but without the hyperlinks.

Maud

Code:

Private Sub Worksheet_Activate()
     For I = 2 To Worksheets.Count:: Cells(I, 1) = Worksheets(I).Name:: Next I
End Sub

Reply | Quote

Maud,

Actually the code will work where ever the Index sheet is located as long as it is named Index. Of course the Index sheet will NOT show up in the Index. I has assumed (oh that nasty word :lol:) the idea was to get to the sheets quickly thus the hyperlinks. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Assuming the Index sheet is the first sheet

RG,

I was referring to the code I posted. It must be in the Worksheet_Activate event subroutine of the first sheet, Worksheet(1), so that the code will display Worksheet(2) and Worksheet(3) as I cycles from 2 to the number of sheets.

Your code is gold as always!

Maud

Reply | Quote

RG,
Your code is gold as always!
Maud

Maud,

Don’t I WISH! :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

When you have spaces in the sheet names you must include the single quote. Try changing the lines of code to

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=””, SubAddress:= _
”‘” & sht.Name & “’!A1″, TextToDisplay:=sht.Name

Steve

Reply | Quote

Hi Steve

I have added the code. I then clicked on the macro btton and then selected a hyperlink. I took me to tjhe required sheet. However, when I select the index sheet and click on any hyperlink, I get “reference is not valid”

Please test & advise

Howard

Reply | Quote

Steve,

Thanks for updating this. I never put spaces in sheet names, a habit developed decades ago, so I keep forgetting about this requirement when I write code. I’ll see if I can cram one more thing into this old cranium as it is always good practice to allow for all possibilities. Thanks again! :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

FYI, without the spaces the single quotes are NOT required but in cases like this it does NOT hurt to add them if not required. using them in INDIRECT , HYPERLINK. etc formulas around the sheet name will not hurt if they are not required (XL will remove them) but are neccessary when required and it is a good habit to just use them even if the names do not require them.

Steve

Reply | Quote

It needs to be added to both places in the code (there are 2 procedures that have that line…)

Steve

Reply | Quote

Hi Steve

Thanks, the macro works perfectly now

Howard

Reply | Quote

Works great, just note that sheet names must not have spaces or dashes. Apparently, these special characters break the code.

That was fixed in later versions of the code if you read further in the thread…

Steve

Reply | Quote

That was fixed in later versions of the code if you read further in the thread…

Steve

Right. I was in quite a frenzy earlier. Got pretty excited and didn’t read through the thread. Got to last part now, though.

Reply | Quote