automatically create list of the worksheets

[Automatically create index of worksheets]

Hello,

I would like to create basically an index or outline page with a list (and
hyperlinks if possible) of all of my worksheet titles or names.

I found the following macros in previous questions, however, I had these 2
problems:
1) It is not recognizing ALL of my worksheets
2) I do not know how to make sure it constently updates

I am a total Macro ROOKIE so please give me the idiot's version of the answer

Here is the macro I found and tried to use:
Sub Sheet_Names()
Dim ws As Worksheet
With Worksheets.Add
.Name = "Sheet Names"
.Move before:=Worksheets(1)
End With
Sheets("Sheet Names").Activate
Range("a1").Activate
For Each ws In Worksheets
If ws.Name <> "Sheet Names" Then
ActiveCell.Formula = ws.Name
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub

 

[Don Guillett]

This will list all sheets in the active workbook in col 1 of the activesheet
Sub listsheetsinthisworkbook()
For i = 1 To Sheets.count
Cells(i, 1) = Sheets(i).Name
Next i
End Sub

Right click sheet tab>view code>put the macro below in the same sheet>Now
when you doubleclick on the sheet you will be taken to it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(WantedSheet) Is Nothing Then
' GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(WantedSheet).Range("g1")
End If
Application.DisplayAlerts = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"Automatically create index of worksheets" <Automatically create index of
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Mike Fogleman]

Not sure why it is not recognizing all worksheets unless some of them are
Chart sheets. Here is a modification to create the hyperlinks you need:

Sub Sheet_Names()
Dim ws As Worksheet
With Worksheets.Add
.Name = "Sheet Names"
' .Move before:=Worksheets(1)'sheets always added on the left
End With
' Sheets("Sheet Names").Activate 'newly added sheet is the active sheet
' Range("a1").Activate 'automatically active on a new sheet
For Each ws In Worksheets
If ws.Name <> "Sheet Names" Then
ActiveCell.Value = ws.Name
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:= _
ws.Name & "!A1", TextToDisplay:=ws.Name
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub

Notice I commented out some unnecessary lines. A note that this macro will
add a new sheet each time it is ran. If you intend to add more sheets and
then run this to update your index page then some more code would be needed
to check if Sheet Names already exists.

Mike F
"Automatically create index of worksheets" <Automatically create index of
(e-mail address removed)> wrote in message
news:[email protected]...

 

[FSt1]

hi
if your are a total macro rookie, you may opt for a non-vba way.
right click the sheet navigator in the lower left of any sheet. you will get
a popup listing all of your sheets. click on to go to it. if you have more
that 15 sheets(i think it 15) you will have an extra entry "more sheets".
click it and you will get a popup scrollable text box with all your sheet
names.

Regards
FSt1

 

[Gord Dibben]

Rather than an index sheet with hyperlinks.......how do you get back to the
index sheet?.............try a sheet navigation toolbar or similar.

Sheet navigation bar from by Dave Peterson at Debra Dalgleish's site.

http://www.contextures.on.ca/xlToolbar01.html

Or Bob Phillips' Browsesheets macro.

See this google search result.

http://tinyurl.com/yoa3dw


Gord Dibben MS Excel MVP