Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

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
0
titorober23
Asked:
titorober23
  • 7
  • 3
  • 2
3 Solutions
 
Rory ArchibaldCommented:
Hi,
Do you mean as a formula in a cell?
0
 
titorober23Author Commented:
yes
0
 
Patrick MatthewsCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 ArchibaldCommented:
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 MatthewsCommented:
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 7
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now