Summary of Worksheet names

[Niamh]

I am trying to create a list of worksheet names on my first worksheet,
similar to a table of contents. Is there a way of linking to the worksheet
names, so this table gets updated automatically?
Thanks,
Niamh

 

[Gary''s Student]

If you run this small macro, it will produce a list of sheets that are
"clickable":

Sub EasyToc()
' gsnuxx
Dim ws As Worksheet
Dim n As String
Dim p1 As String, dq As String, sq As String
dq = Chr(34)
sq = Chr(39)
p1 = "=HYPERLINK(" & dq & "#'"
p2 = "'!A1" & dq & "," & dq
k = 1
For Each ws In Worksheets
n = ws.Name
Cells(k, 1).Formula = p1 & n & p2 & n & dq & ")"
k = k + 1
Next
End Sub

 

[Mike]

'Do right click on the sheet that will be your summary sheet. Select view
code.
'Paste this into the sheet module. Now go back to your workbook click any
'other sheet in your workbook. Then click back on your summary sheet.

Private Sub Worksheet_Activate()
Dim ws As Worksheet
Dim i As Long
i = 3
Columns("A:A").ClearContents
For Each ws In Worksheets
If ws.Name <> ActiveSheet.Name Then
Range("A" & i).Value = ws.Name
i = i + 1
End If
Next ws
End Sub

 

[Fanny]

Dear Gary,

The macro is very very useful to me. Moreover, if I want to list out the
worksheet names with hyperlink done automatically of another workbook, how
should I modify the macro?

Thanks in advance.

Fanny

 

[Bob Phillips]

Sub EasyToc()
' gsnuxx
Dim ws As Worksheet
Dim n As String
Dim p1 As String, dq As String, sq As String
dq = Chr(34)
sq = Chr(39)
p1 = "=HYPERLINK(" & dq & "#'"
p2 = "'!A1" & dq & "," & dq
k = 1
For Each ws In Workbooks("some book.xls").Worksheets
n = ws.Name
Cells(k, 1).Formula = p1 & n & p2 & n & dq & ")"
k = k + 1
Next
End Sub

 

[Fanny]

Dear Bob,

Your suggestion has improved my problem that worksheet name listing can be
retrieved from a protected workbook, provided that I must manually open the
source workbook. However, I still need to manually make hyperlink from the
new listing to link to the protected workbook to ease the checking.

Pls check if I can change the workbook name (excel file name) at the
worksheet like defined names or enter the excel file name at a designated
cell rather than via the macro because I need to retrieve the worksheet names
from protected workbooks frequently. My problem is that I need to fill up
package files from Head Office and each workbook has many worksheets with
similar names which can be easily missed.

With Gary's macro, I can retrieve all the worksheet names and automatically
hyperlinked to the selected worksheet. However, I cannot apply the said
macro when the workbook is protected.

Thanks for your valuable assistance.

Fanny