VBA to rename sheet WITHIN Excel

[Guest]

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

 

[Bob Phillips]

Activesheet.Name = "myName"

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

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).

 

[Guest]

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!