Excel - ActiveWorkbook.Names.Add Name ???

Asked By Luc on 16-Jan-10 07:00 AM
2 Questions :

1 How do i refer to the cells wich are currently selected ?

So, i want to assign a name to the cells which are selected.

2 Is it possible to assign the same name to ranges in different worksheets ?
Example : i have  sheets "Step 1" to......"Step xx"
I want a name assigned to a range in "Step 1", but that name can also
exist in the sheet "Step 1" or "Step xx".



Barb Reinhardt replied to Luc on 16-Jan-10 07:46 AM
Is it possible to assign the name name to ranges on different worksheets?

You bet, as long as the scope of the name is the worksheet of interest.   In
Excel 2007, under FORMULAS -> DEFINE NAME, it defaults to WORKBOOK, but you
can change it to the worksheet of interest.

If you want to do it programmatically, do something like this

aWS.Names.Add Name:=myRangeString, RefersTo:=myRefersTo

I have defined myRefersto this way in my code

myRefersTo = "='" & aWS.Name & "'!" &

where myRange defined as a range in my code.


Barb Reinhardt
Luc replied to Barb Reinhardt on 16-Jan-10 08:58 AM
Is this also for Excel 2003 ?

Luc replied to Luc on 16-Jan-10 09:09 AM
Never mind, it figured it out :

ActiveSheet.Names.Add Name:="Asbest", RefersTo:=Selection
Dave Peterson replied to Luc on 16-Jan-10 09:26 AM
Selection.name = "Step1"
will create a global/workbook level name.

with selection
.name = "'" & .parent.name & "'!" & "Step1"
end with
will create a local/worksheet level name.

And it is not necessary to select the range to work with it.

with worksheets("sheet999").range("A1")
.name = "'" & .parent.name & "'!" & "Step1"
end with


Dave Peterson