sheet names

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John - 26 Jul 2006 04:05 GMT

What returns the name of a worksheet in a workbook if you are in the
workbook.

for instance: findit(1) gives the name of sheet 1.
Thanks
John

Reply to this Message

Max - 26 Jul 2006 04:24 GMT

> What returns the name of a worksheet in a workbook
> if you are in the workbook.
> for instance: findit(1) gives the name of sheet 1.

Try this technique, from a post by Harlan

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas, etc. It will auto-extract the sheetname implicitly.
To test it, just enter in any cell in any sheet*: =WSN
and the name of that sheet will be returned

*Note: Workbook must be saved for the above to work, ie a name given to book

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

John - 26 Jul 2006 13:37 GMT

Amazing! Thank you. It seems strange excel doesn't have something to do
this.

John

>>What returns the name of a worksheet in a workbook
>>if you are in the workbook.
[quoted text clipped - 15 lines]
>
> *Note: Workbook must be saved for the above to work, ie a name given to book

Reply to this Message

SteveW - 26 Jul 2006 13:56 GMT

'Cos 99% of users can just read the sheet name.
anyway no need to have a function for everything that can be provided
using the existing tools/functions :)

Steve

> Amazing! Thank you. It seems strange excel doesn't have something to do
> this.
[quoted text clipped - 17 lines]
>> *Note: Workbook must be saved for the above to work, ie a name given
>> to book

Reply to this Message

John - 26 Jul 2006 19:43 GMT

I am writing macros. I have about 150-200 sheets. I want to either find
something or insert something by sheet name in several of my programs.
Apparently there's no way to do it in existing excel functions. It would
seem fundemental to me to be able to find out the name of, say, sheet 42
in my program.

John

> 'Cos 99% of users can just read the sheet name.
> anyway no need to have a function for everything that can be provided
[quoted text clipped - 24 lines]
>>> *Note: Workbook must be saved for the above to work, ie a name
>>> given to book

Reply to this Message

Max - 27 Jul 2006 02:39 GMT

> I am writing macros. I have about 150-200 sheets. I want to either find
> something or insert something by sheet name in several of my programs.
> Apparently there's no way to do it in existing excel functions. It would
> seem fundemental to me to be able to find out the name of, say, sheet 42
> in my program.

Perhaps you should have mentioned this in your original post. Perhaps you
should have posted in .programming, instead of here, in .newusers (if it's a
vba question, you're hardly a "new user").

Hang around awhile for possible insights from other responders versed in
vba. If none drops by, suggest you put in a fresh post in .programming.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Bob Phillips - 27 Jul 2006 10:00 GMT

Here's a little UDF to do it

Public Function Findit(idx As Long)
Findit = Application.Caller.Parent.Parent.Sheets(idx).Name
End Function

if you want to call it from VBA, use this

Public Function Findit(idx As Long, Optional wb)
If IsMissing(wb) Then
Set wb = ThisWorkbook
End If
Findit = wb.Sheets(idx).Name
End Function

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> > I am writing macros. I have about 150-200 sheets. I want to either find
> > something or insert something by sheet name in several of my programs.
[quoted text clipped - 8 lines]
> Hang around awhile for possible insights from other responders versed in
> vba. If none drops by, suggest you put in a fresh post in .programming.

Reply to this Message