worksheet collection issue
 

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 ?

worksheet collection issue
 

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 boxEnter

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

worksheet collection issue
 

Same as this morning.

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

--
Regards,
Tom Ogilvy

Mark Kubicki wrote in message
...
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 ?