Sheets names

[Petros]

Hello again. Here is a different one.
How can I produce a list (in one sheet) of all the sheets names in a file?
Is there a function?
Thanks
Petros

 

[Gary''s Student]

First enter this UDF:

Function sheetname(i As Integer) As String
sheetname = Sheets(i).Name
End Function

then in the worksheet enter:

=sheetname(ROWS($A$1:$A1)) and copy down.

 

[Boss]

This macro should solve your purpose.

Sub sheetnames()

RowCount = 1
For Each sht In Sheets
Range("A" & RowCount) = sht.Name
RowCount = RowCount + 1
Next sht

End Sub


Boss

 

[Jim May]

FWIW,,,
This code is producing the Code (Hidden) Name of the Sheet, Not the
Sheetname the user has access to by renaming with the short-cut menu.
Jim

 

[Dave Peterson]

I don't think so.

That would be:
sheetname = Sheets(i).CodeName

 

[Jim May]

Good Catch Dave;

Not sure what I did -- the first time -- but,,,
I can't redo.

Jim