The macro is setup to populate the activesheet. So if you select the contents
sheet and run it you will get the list there. If you then select the lookup
sheet and run the macro again you will get the list of hyperlinks on the
lookup sheet as well.

Rowan

"Phil Osman" wrote:

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