Hyperlink Question

[Guest]

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 Cone]

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

 

[Guest]

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]

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

 

[Guest]

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