VBA Inactive Worksheets

I am having trouble referencing a range in an inactive worksheet to feed an vba array. this is the following vba code that does not work:

Please Login or Register  to view this content.

The error I get is:

Run-time error '1004'

Application-defined or object-defined error

It will work if I activate the sheet but I'm trying to avoid that. The code that will work but trying to avoid is:

Please Login or Register  to view this content.

You need to specify the Workbook when the worksheet is not active (From Microsoft):

Ranges on Inactive Worksheets

If you want to work efficiently with more than one worksheet at the same time, it is important to be able to refer to ranges on worksheets without having to activate those worksheets. Switching between worksheets is slow, and code that does this is more complex than it need be. This also leads to code that is harder to read and debug.

All our examples so far apply to the active worksheet, because they have not been qualified by any specific worksheet reference. If you want to refer to a range on a worksheet that is not active, simply use the Range property of the required Worksheet object:

Please Login or Register  to view this content.

If the workbook containing the worksheet and range is not active, you need to further qualify the reference to the Range object as follows:

Please Login or Register  to view this content.

jrdnoland

Thanks so much... I knew that but after a long day you start to make mistakes. That's exactly what's going on. I'm declaring the WS but not the WB.

Many Thanks