Solved

Excel sheet names

Posted on 2009-03-31
12
224 Views
Last Modified: 2013-12-17
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
Comment
Question by:titorober23
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
12 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24028559
Hi,
Do you mean as a formula in a cell?
0
 

Author Comment

by:titorober23
ID: 24028561
yes
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 75 total points
ID: 24028573
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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24028581
titorober23,

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

Regards,

Patrick
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24028596
Typo...

Function SheetName(SheetIndex As Long)
 
    Application.Volatile
    SheetName = ThisWorkbook.Sheets(SheetIndex).Name
 
End Function
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24028613
Hm, it appears that it is automatically updating on a sheet rename--must be because the renaming itself
forces a recalc :)
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 50 total points
ID: 24028648
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24028669
Oops, should have refreshed!
0
 

Author Comment

by:titorober23
ID: 24028704
it is giving me #NAME?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24028717
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24028726
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
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 75 total points
ID: 24028736
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question