[nc]

Hi

Is there a macro that list all the sheet's names of a workbook in
alpabetical order and by double clicking the relevant name on the list select
the worksheet.

Or

Macro that can find/select worksheet by key words in the worksheetname.

[Mike H]

Hi,

Right click any sheet tab, view code and paste this in and run it

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

"nc" wrote:

Hi

Is there a macro that list all the sheet's names of a workbook in
alpabetical order and by double clicking the relevant name on the list select
the worksheet.

Or

Macro that can find/select worksheet by key words in the worksheetname.

[nc]

Hi Mike

Thanks. It works except for sheet names with a spaces within it. I get the
message box, "Reference ids not valid".

Is there a way I can fix the macro?

Thanks.



"Mike H" wrote:

Hi,

Right click any sheet tab, view code and paste this in and run it

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

"nc" wrote:

Hi

Is there a macro that list all the sheet's names of a workbook in
alpabetical order and by double clicking the relevant name on the list select
the worksheet.

Or

Macro that can find/select worksheet by key words in the worksheetname.

[Bob Phillips]

See response in other thread, you need to enclose worksheet name in single
quotes.

--
HTH

Bob

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

"nc" wrote in message
...
Hi Mike

Thanks. It works except for sheet names with a spaces within it. I get
the
message box, "Reference ids not valid".

Is there a way I can fix the macro?

Thanks.



"Mike H" wrote:

Hi,

Right click any sheet tab, view code and paste this in and run it

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

"nc" wrote:

Hi

Is there a macro that list all the sheet's names of a workbook in
alpabetical order and by double clicking the relevant name on the list
select
the worksheet.

Or

Macro that can find/select worksheet by key words in the worksheetname.