Sheet name list
 

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

Sheet name list
 

Hi,

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

Mike

"nc" wrote:

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

Sheet name list
 

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.

"Mike H" wrote:

Hi,

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

Mike

"nc" wrote:

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

Sheet name list
 

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


--
Gary''s Student - gsnu200794


"nc" wrote:

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

Sheet name list
 

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)

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

"Mike H" wrote:

Hi,

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

Mike

"nc" wrote:

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