refering to a named range on a different worksheet

[robert.hatcher]

I have code that refers to a named range:

Dim name1, name2, varRange, c, rowCount
name2 = ActiveWorkbook.Names("Range")
rowCount = ActiveSheet.Range(name2).Value 'Original

"Range" is a named range on Sheet2

If sheet 2 is active when I run the code the value of "Range" loads
correctly.

However, I need this to work when I have sheet1 active. Trying this I
get "Application-defined or object-defined error" probably because
of the "activesheet" object...

I tried: rowCount = ActiveWorkbook.Range(name2).Value
and get "Object doesn't support this property or method (Error
438)"

Clearly Im missing something here, any help will be appreciated.

 

[Sandy]

You need to tell what sheet the named range is on, try this in your
code:

rowCount = Worksheets("Sheet2").Range(name2).Value

Sandy

 

[robert.hatcher]

thanks Sandy, that works!

You need to tell what sheet the named range is on, try this in your
code:

rowCount = Worksheets("Sheet2").Range(name2).Value

Sandy