Sheet name

N

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.
 
M

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
 
N

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.
 
B

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