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

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.
--
Gary''s Student - gsnu200771

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


"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200771

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

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200771

I don't think so.

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



Jim May wrote:

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

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200771

--

Dave Peterson

Good Catch Dave;

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

Jim

"Dave Peterson" wrote:

I don't think so.

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



Jim May wrote:

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

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200771

--

Dave Peterson