Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
list sheet 1 - same cell (eg A1) in a group of worksheets sheet | Excel Worksheet Functions | |||
Filter Range on Sheet B Based on List on Sheet A | Excel Discussion (Misc queries) | |||
TAKING INFO FROM ONE SHEET AND PRODUCING A LIST IN ANOTHER SHEET | Excel Discussion (Misc queries) | |||
sort list of players by team from player list on separate sheet | Excel Worksheet Functions | |||
adding data from one sheet to another sheet as a dropdown list bo. | Excel Discussion (Misc queries) |