Sheets names
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 |
Sheets names
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 |
Sheets names
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 |
Sheets names
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 |
Sheets names
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 |
Sheets names
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 |
All times are GMT +1. The time now is 02:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com