Refers to active cells

[Miree]

I have this line of code is there a way to change the RefersTo section to
ReferTo the active cells?

ActiveWorkbook.Names.Add Name:="OBM", RefersToR1C1:="=Densities!R4C6:R139C6"

Thank you

 

[Per Jessen]

Hi

This should do it:

MyList = Selection.Address
ActiveWorkbook.Names.Add Name:="OBM", RefersTo:="=Densities!" & MyList

Regards,
Per

 

[papou]

Hi
The answer is Yes:
ActiveWorkbook.Names.Add Name:="OBM", RefersToR1C1:=Selection.Address

But I would be careful about that.

HTH
Cordially
Pascal

 

[papou]

Since active cells could be from another sheet:
shname = ActiveSheet.Name & "!"
ActiveWorkbook.Names.Add Name:="OBM", RefersToR1C1:=shname &
Selection.Address

But I still think this is a little dangerous.

HTH
Cordially
Pascal

 

[Miree]

This worked well, but came accross a problem, one of the ranges i need
defined is a combination of two ranges WBM and OBM, is there a way to make
both named ranges active and to name?

Thank you

 

[papou]

Miree
To select multiple ranges, hold the CTRL key.

Cordially
Pascal

 

[Miree]

That doesn't work when you need to select two named ranges, but i have
decided i dont need it anymore anyways but thatnks for all the advice

 

[Dave Peterson]

Another:

with selection
.name = "'" & .parent.name & "'!" & "OBM"
end with

Ps. I wouldn't use this name.

If you ever upgrade to xl2007 (with 16k columns), this will be an invalid
name--it looks too much like a column reference.

I'd use "_OBM"