Dim rngSource As Range
Dim Mybook as Workbook
Set Mybook = Workbooks.Open ("C:\Documents and Settings\" & _
"abevan\MyDocuments\Temp\August 05\SMVIL Project Log.xls")

set rngSource = Mybook.Sheets("Project Log Form").Range("A8:U79")

--
Regards,
Tom Ogilvy


"Andibevan" wrote in message
...

"Dick Kusleika" wrote in message
...
Andibevan wrote:
Hi All,

Can someone tell me if the following code is possible:- I get an
"Object does not support this property or method"

Can use arrays in this fashion - Set sourceRNG = myBook.rngSource(i).

I am trying to Open a workbook and copy an array of source ranges to
an array of destination ranges.

Here is an extract from my code:-


Set rngSource(1) = Worksheets("Sheet 1").Range("A8:U79")
Set rngSource(2) = Worksheets("Sheet 2").Range("A7:X10")

Workbooks.Open (SRCbook)
Set myBook = ActiveWorkbook
For i = 1 To UBound(rngSource)


Set sourceRNG = myBook.rngSource(i)

Your code Set myBook = Workbooks.Open(SRCbook) from yesterday's post
works
for me. I don't know why you'd be getting an error message on that
line.
For copying ranges, I'm not sure why you would use an array. Just copy
the
ranges using the Copy method.

Can you open Workbooks(SRCbook) through the UI by using File Open?
Maybe
it's not properly formatted and that's why you're getting the error.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com


Hi Dick,

That part also works for me. The part that doesn't work is Set sourceRNG
=
myBook.rngSource(i). I am using arrays as I have about 100 references and
it makes the code more transferable in the future.

I have tried your suggestion and I am able to open the file.
Additionally -
I presume that if SRCbook was formatted incorrectly it wouldn't open the
workbook (which it does).

I think if I could get the following to work it would help me sort out my
problem:-

Dim rngSource As Range
Set rngSource = Workbooks("C:\Documents and Settings\abevan\My
Documents\Temp\August 05\SMVIL Project Log.xls").Sheets("Project Log
Form").Range("A8:U79")

It says "Subscript out of range".

Thanks

Andi