Excel sheet names

Hi Guys
I am planning to do a report and i need a list of all sheetnames in one colums,is there a function that i can use to get sheet names
F(1) = Sheet#1's name
F(2) =Sheet#2's name
and so on
titorober23Asked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
The following UDF will return sheet names.  Be advised that it will not update automatically when you change
a sheet name, but it should update at the next recalc.
Function SheetName(SheetIndex As Long)
 
    Application.Volatile
    SheetName = ThisWorkbook.Sheets(Sheet Index).Name
 
End Function

Open in new window

0
 
Rory ArchibaldCommented:
Hi,
Do you mean as a formula in a cell?
0
 
titorober23Author Commented:
yes
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Patrick MatthewsCommented:
titorober23,

Note also that that is a VBA, and not a C#, function...

Regards,

Patrick
0
 
Patrick MatthewsCommented:
Typo...

Function SheetName(SheetIndex As Long)
 
    Application.Volatile
    SheetName = ThisWorkbook.Sheets(SheetIndex).Name
 
End Function
0
 
Patrick MatthewsCommented:
Hm, it appears that it is automatically updating on a sheet rename--must be because the renaming itself
forces a recalc :)
0
 
Rory ArchibaldConnect With a Mentor Commented:
You could use a UDF like this:
 

Function SheetName(lngIndex As Long) As String
SheetName = Application.Caller.Parent.Parent.Sheets(lngIndex).Name
End Function

Open in new window

0
 
Rory ArchibaldCommented:
Oops, should have refreshed!
0
 
titorober23Author Commented:
it is giving me #NAME?
0
 
Patrick MatthewsCommented:
titorober23,

Rory's use of the Caller method is a good one--my approach works breaks down if the UDF is used in an add-in
or personal macro workbook.  However, I think you need the Volatile bit to ensure updates when you change the
sheet names and/or order.  Combining the best bits of both...

Regards,

Patrick
Function SheetName(lngIndex As Long)
 
    With Application
        .Volatile
        SheetName = .Caller.Parent.Parent.Sheets(lngIndex).Name
    End With
 
End Function

Open in new window

0
 
Patrick MatthewsCommented:
rorya said:
>>Oops, should have refreshed!

No worries, Rory--indeed, your post pointed to a better way of figuring out which Workbook to process :)
0
 
Patrick MatthewsConnect With a Mentor Commented:
titorober23 said:
>>it is giving me #NAME?

Did you put the code in a regular VBA module, and not a sheet or ThisWorkbook?
Are macros enabled?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.