Phil,

Just add another variable to hold the row.

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

Robin Hammond
www.enhanceddatasystems.com

"Phil Osman" wrote in message
...
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