This works great!

Is there anyway to now get the list of worksheet names to be returned on
more than one sheet ?
eg. I have a contents sheet that I want to show the list when I run the
macro, but also I have a lookup sheet that I want to show the updated list.


"Rowan" wrote:


Try this:

Sub sheets_list()
Dim a As Long
Dim s As Long
Dim shtName As String
s = 4
For a = 1 To Sheets.Count
shtName = Sheets(a).Name
Sheets(a).Hyperlinks.Add anchor:=Cells(a+4, s), Address:="", SubAddress:="'" _
& shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName
Next a
End Sub


"Phil Osman" wrote:

Sub sheets_list()
Dim a As Long
Dim s As Long
Dim shtName As String
s = 4
For a = 1 To Sheets.Count
shtName = Sheets(a).Name
Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _
& shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName
Next a
End Sub

My understanding is that the s = 4 determines the column where the list of
worksheets should be returned, and this seems to be correct when I change the
value. Also if I change a = 1 to a = 5 it starts the list in Cell D5 which is
what I want. BUT......changing to a = 5 makes the macro only start returning
the names from Sheet 5 in the workbook onwards.

I want to return the entire list of worksheets in Cell D5.

Can someone help please as I'm sure there is just something easy I need to
change.....thanks!

Phil