Sheet name list

[nc]

The following macro (list links of worksheets of workbook) was posted by Mike
H. It does not work if the sheet name contains a space. Is there any way I
can change the macro to make it work.

Thanks.

Sub sonic()
Sheets("Sheet1").Select
For x = 1 To Worksheets.Count
Cells(x, 1).Hyperlinks.Add Anchor:=Cells(x, 1), Address:="", _
SubAddress:=Worksheets(x).Name & "!A1", TextToDisplay:=Worksheets(x).Name
Next
lastrow = Sheets("sheet1").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:A" & lastrow).Sort Key1:=Range("A1"), Order1:=xlAscending
End Sub

 

[Mike H]

Hi,

What part of it doesn't work if there's a space?

Mike

 

[nc]

Hi Mike

When you click on a link of a sheet name with a space (e.g. IFC Course), I
get a message ""Reference is not valid".

Thanks.

 

[Gary''s Student]

A very slight change:

Sub sonic()
sq = Chr(39)
Sheets("Sheet1").Select
For x = 1 To Worksheets.Count
Cells(x, 1).Hyperlinks.Add Anchor:=Cells(x, 1), Address:="", _
SubAddress:=sq & Worksheets(x).Name & sq & "!A1",
TextToDisplay:=Worksheets(x).Name
Next
lastrow = Sheets("sheet1").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:A" & lastrow).Sort Key1:=Range("A1"), Order1:=xlAscending
End Sub

 

[Bob Phillips]

Sub sonic()
Dim x As Long, lastrow As Long

Sheets("Sheet1").Select
For x = 1 To Worksheets.Count
Cells(x, 1).Hyperlinks.Add Anchor:=Cells(x, 1), Address:="", _
SubAddress:="'" & Worksheets(x).Name & "'!A1",
TextToDisplay:=Worksheets(x).Name
Next
lastrow = Sheets("sheet1").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:A" & lastrow).Sort Key1:=Range("A1"), Order1:=xlAscending
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)