named ranges...need to refer to the worksheet??

[daveyc18]

hi when using named ranges in vba....i found that you dont necessarily need to refer to the worksheet


e.g let's say i have named range "desk" in the tab "A"

usually in VBA i can just do range("desk").copy

but for this one particular file, it doesn't work...i actually need to do this:

sheets("a").range("desk").copy



Why is that???

 

[Fluff]

Sounds like that name is scoped at sheet level, rather than workbook level

 

[Marcelo Branco]

I suspect the scope of the named range "desk" is Worksheet, not Workbook (check on Names Manager) and that the active sheet when your code reaches this line is not sheet "a".

M.

 

[daveyc18]

thank you, but how do you change it? it's greyed otut

 

[Fluff]

Not sure you can.
You'll probably need to delete the range & then re-set it.

 

[Marcelo Branco]

thank you, but how do you change it? it's greyed otut

I do not think you should change the scope (risky) - maybe there is some reason for that to be so.
Use
Sheets ("a").Range("desk").Copy
or
Sheets ("a"). Activate
Range ("desk"). Copy

M.

 

[daveyc18]

Not sure you can.
You'll probably need to delete the range & then re-set it.

some googling ...a guy simply went to that named ranged...."defined" the name agai via Name Manager and then, of course, Excel told him the name was already in use....yet after he pressed OK it worked! unfortuantely, didnt work for me as he did it in excel 2007 (im using 2010)

oh well..