worksheet collection issue

[Mark Kubicki]

trying to get a reference to a named range in a different (open) worksheet:

this result in an error (9):
Workbooks("Book1.xls").Worksheets("Sheet1").Range("RateDesigner")


when i type the final dot after: workbooks("Book1.xls").
i get a drop-down list that includes: worksheets

when i type the final dot after : worksheets().
i get a drop-down list; but, it does not include: range

when i type the final dot after worksheets("Sheet1").
i do not get even a drop-down list


suggestion ?

 

[gocush]

I don't see anything wrong with your code so I tested it as follows:

I opened and new wbk (Book1.) and picked any cell and type
RateDesigner in the Name box>>Enter

I typed Hello in this cell.

I then opened Book2, opened the VBE, inserted a new module and create
a sub with the following one line:


Activecell
Workbooks("Book1.xls").Worksheets("Sheet1").Range("RateDesigner")

When I ran the code it returned "Hello" to the activecell a
expected.

So, QUESTIONS: HAVE YOU NAMED THE RANGE IN BOOK1: RateDesigner?

In your code, do you have a range (Activecell or G2 etc) or a variabl
where you want to store the results?

Using a Variable you could say something like:
Dim MyVar as string
MyVar
Workbooks("Book1.xls").Worksheets("Sheet1").Range("RateDesigner")

The results would then be stored in MyVar for the duration of your su
( or longer if declared as Public

 

[Tom Ogilvy]

Same as this morning.

set rng = Workbooks("Job Nos for Timesheets.xls"). _
Names("RateDesigner").RefersToRange