ActiveWorkbook.Names.Add Name ???

  • Thread starter Thread starter Luc
  • Start date Start date
L

Luc

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


Thanks,

Luc
 
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've defined myRefersto this way in my code

myRefersTo = "='" & aWS.Name & "'!" &
myRange.AddressLocal(ReferenceStyle:=xlA1)

where myRange defined as a range in my code.


HTH,

Barb Reinhardt
 
Is this also for Excel 2003 ?

Luc




Barb Reinhardt said:
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've defined myRefersto this way in my code

myRefersTo = "='" & aWS.Name & "'!" &
myRange.AddressLocal(ReferenceStyle:=xlA1)

where myRange defined as a range in my code.


HTH,

Barb Reinhardt
Click to expand...
 
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's not necessary to select the range to work with it.

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