Hyperlink Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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
 
Back
Top