I have the following code:

Sub sheets_list()
Range("M1:M100").Select
Selection.ClearContents
Range("M1").Select
r = ActiveCell.Row
s = ActiveCell.Column
For a = 0 To Sheets.Count - 1
Cells(r + a, s).Value = Sheets(a + 1).Name
Next a
End Sub

It returns a list of all sheets in my workbook.
Is it possible to amend the code so that it returns each of the sheet names
as a hyperlink to the corresponding sheet ?

TIA,
Phil

Phil,

I assume you have the list on the last sheet in the workbook.
With some modification to your code, I came up with the following.
Note: that Xl97 hyperlink code would be slightly different and
that you cannot hyperlink to a chart sheet...
'--------------------------------------------------
Sub sheets_list()
Dim a As Long
Dim s As Long
Dim shtName As String
s = 13
For a = 1 To Sheets.Count - 1
shtName = Sheets(a).Name
Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _
& shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName
Next a
End Sub
'------------------------------------------------
Regards,
Jim Cone
San Francisco, USA



"Phil Osman" wrote in message
...
I have the following code:

Sub sheets_list()
Range("M1:M100").Select
Selection.ClearContents
Range("M1").Select
r = ActiveCell.Row
s = ActiveCell.Column
For a = 0 To Sheets.Count - 1
Cells(r + a, s).Value = Sheets(a + 1).Name
Next a
End Sub

It returns a list of all sheets in my workbook.
Is it possible to amend the code so that it returns each of the sheet names
as a hyperlink to the corresponding sheet ?

TIA,
Phil

Jim,

That works well, but the sheet I am returning the list to is the 2nd sheet
in the workbook. Maybe because of this the macro does not return the last
sheet in the workbook.
Any ideas?

Phil

"Jim Cone" wrote:

Phil,

I assume you have the list on the last sheet in the workbook.
With some modification to your code, I came up with the following.
Note: that Xl97 hyperlink code would be slightly different and
that you cannot hyperlink to a chart sheet...
'--------------------------------------------------
Sub sheets_list()
Dim a As Long
Dim s As Long
Dim shtName As String
s = 13
For a = 1 To Sheets.Count - 1
shtName = Sheets(a).Name
Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _
& shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName
Next a
End Sub
'------------------------------------------------
Regards,
Jim Cone
San Francisco, USA



"Phil Osman" wrote in message
...
I have the following code:

Sub sheets_list()
Range("M1:M100").Select
Selection.ClearContents
Range("M1").Select
r = ActiveCell.Row
s = ActiveCell.Column
For a = 0 To Sheets.Count - 1
Cells(r + a, s).Value = Sheets(a + 1).Name
Next a
End Sub

It returns a list of all sheets in my workbook.
Is it possible to amend the code so that it returns each of the sheet names
as a hyperlink to the corresponding sheet ?

TIA,
Phil

Phil,

The last sheet is purposely omitted in this line...
For a = 1 To Sheets.Count - 1

Remove the "-1" to include the last sheet.

Regards,
Jim Cone
San Francisco, USA


"Phil Osman" wrote in message
...
Jim,

That works well, but the sheet I am returning the list to is the 2nd sheet
in the workbook. Maybe because of this the macro does not return the last
sheet in the workbook.
Any ideas?

Phil

"Jim Cone" wrote:

Phil,
I assume you have the list on the last sheet in the workbook.
With some modification to your code, I came up with the following.
Note: that Xl97 hyperlink code would be slightly different and
that you cannot hyperlink to a chart sheet...
'--------------------------------------------------
Sub sheets_list()
Dim a As Long
Dim s As Long
Dim shtName As String
s = 13
For a = 1 To Sheets.Count - 1
shtName = Sheets(a).Name
Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _
& shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName
Next a
End Sub
'------------------------------------------------
Regards,
Jim Cone
San Francisco, USA



"Phil Osman" wrote in message
...
I have the following code:

Sub sheets_list()
Range("M1:M100").Select
Selection.ClearContents
Range("M1").Select
r = ActiveCell.Row
s = ActiveCell.Column
For a = 0 To Sheets.Count - 1
Cells(r + a, s).Value = Sheets(a + 1).Name
Next a
End Sub
It returns a list of all sheets in my workbook.
Is it possible to amend the code so that it returns each of the sheet names
as a hyperlink to the corresponding sheet ?

TIA,
Phil

Works perfectly now, thanks indeed Jim !
========

"Jim Cone" wrote:

Phil,

The last sheet is purposely omitted in this line...
For a = 1 To Sheets.Count - 1

Remove the "-1" to include the last sheet.

Regards,
Jim Cone
San Francisco, USA


"Phil Osman" wrote in message
...
Jim,

That works well, but the sheet I am returning the list to is the 2nd sheet
in the workbook. Maybe because of this the macro does not return the last
sheet in the workbook.
Any ideas?

Phil

"Jim Cone" wrote:

Phil,
I assume you have the list on the last sheet in the workbook.
With some modification to your code, I came up with the following.
Note: that Xl97 hyperlink code would be slightly different and
that you cannot hyperlink to a chart sheet...
'--------------------------------------------------
Sub sheets_list()
Dim a As Long
Dim s As Long
Dim shtName As String
s = 13
For a = 1 To Sheets.Count - 1
shtName = Sheets(a).Name
Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _
& shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName
Next a
End Sub
'------------------------------------------------
Regards,
Jim Cone
San Francisco, USA



"Phil Osman" wrote in message
...
I have the following code:

Sub sheets_list()
Range("M1:M100").Select
Selection.ClearContents
Range("M1").Select
r = ActiveCell.Row
s = ActiveCell.Column
For a = 0 To Sheets.Count - 1
Cells(r + a, s).Value = Sheets(a + 1).Name
Next a
End Sub
It returns a list of all sheets in my workbook.
Is it possible to amend the code so that it returns each of the sheet names
as a hyperlink to the corresponding sheet ?

TIA,
Phil