macro to take worksheet names and create dynamic list/range

[maijiuli]

Hello,

This will be a big question so I will ask in steps so I don't overwhelm one
person.

I have a file that contains over 50 worksheets and continues to add more
worksheets. I would like to set up an index worksheet to be the first
worksheet. This worksheet will be named "Index". In this index worksheet I
want to create a drop down list where you could choose which worksheet you
want to go to and once you select your desired worksheet and hit a
command/execution button, it will take you there.

So I guess my first step is to create the dynamic list. My first worksheet
name is "Index". I created another worksheet named "data" just in case I
will need to keep the Dynamic Named Range hidden so others don't see the
background. The other worksheets are names of Insurance carriers: Blue
Shield, HealthNet, Kaiser, Delta Dental, etc....

Q1. How can I create a list (dynamic) that will take each worksheet name and
put it in the list automatically so I don't have to copy/paste each worksheet
name - for existing worksheets and new worksheets.

Thank you for your time,

 

[Joel]

How is this code?

Sub addindex()

'test for inded
found = False
For Each ws In Worksheets
If ws.Name = "Index" Then
found = True
Exit For
End If
Next ws
If found = True Then
Sheets("Summary").Activate
Else
Worksheets.Add _
Before:=Worksheets(1)
ActiveSheet.Name = "Index"
End If

RowCount = 1
For Each ws In Worksheets
Cells(RowCount, "A") = ws.Name
RowCount = RowCount + 1
Next ws

End Sub

 

[maijiuli]

Thanks for looking Joel.

So I copy/pasted your macro into VBA editor under the "data" worksheet and
pressed play. I got the error:

Run-time error '9':
Subscrip out of range

Sorry, I'm so dumb, I probably did it wrong.

 

[Joel]

1)Which line is highlighted when the error occurs?
2) Did any sheet names appear on the index worksheet?
3) did a worksheet Index get created?

 

[maijiuli]

Hey Joel,

I think I did it. I inserted a new module and then copy/pasted your macro
into here. So then I changed the word "Summary" in the macro to "data" and
looked back at the "data" worksheet and all the worksheet names are there!
Awesome! So if I add a new worksheet will the list just keep expanding?