I am using the active sheet name in conjuction with the indirect function as
part of a vlookup to create a list of email addresses, so that the code can
populate the To: field in outlook. I therefore needed something in the
worksheet and not the code, as this is I think easier to manage as the steps
are clearer in the spreadsheet and also makes the code easier for a begginer
in VBA.

It might well be a "round the houses" approach, but it works

Thanks for all the help

"Bob Phillips" wrote:

BTW, why can't you just pick up Activesheet.Name?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bob Phillips" wrote in message
...
Put this in the hidden sheet cell

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,99)

and just make sure that you force a book calculation before reading it in
VBA.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Richard" wrote in message
...
Hi

I need to return the active sheet name in a cell on another sheet. I
guess
the best way of doing this would be to use a UDF, but I don't know how to
set
this up.

For example on a sheet called Names in cell A1, I would like to always
return the name of the currently active sheet. The Names sheet is
hidden,
when in general use.

Thanks in advance
Richard