Thanks Tom, building it up as a string and referencing the string hadn't
occurred to me - I'll use that. And double thanks that I'm no longer going to
drive myself insane trying to do something that can't be done!


"Tom Ogilvy" wrote:

Names are limited to a range. So you can not name a worksheet or a
workbook. You can define a name to hold a string and then use that string
with indirect to build a range reference, but the string would be static and
not be automatically changed if the sheet name or workbook name were to be
changed. (note: indirect only works when creating references to
locations in open workbooks).

--
Regards,
Tom Ogilvy

"DS" wrote in message
...
Hi,
I'm looking to be able to rename a separate workbook within excel, in the
same way as a range can be named using the line:

ActiveWorkbook.Names.Add "ThisRange", RefersTo:=Selection

This is so that formulae within an excel sheet can reference to the named
sheet (depending on previously determined parameters, the named sheet
could
be one of a number of data sources), using COUNTIF etc functions.

Many Thanks in Advance
Mike