Copy data from workbooks
 

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)

Copy data from workbooks
 

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 = rngSource(i)

would be one guess. rngSource is already a range reference, so it is
specific to the workbook and worksheet to which it refers.

If you want to use the address of that reference to refer to the same range
in another workbook you could do


Set sourceRNG = Mybook.Range(rngSource(i).address)

--
Regards,
Tom Ogilvy



"Andibevan" wrote in message
...
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)

Copy data from workbooks
 

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

Copy data from workbooks
 

"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

Copy data from workbooks
 

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

Copy data from workbooks
 

actually that should be:

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

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

Set sourceRNG = Mybook.worksheets( _
"Project Log Form").Range(rngSource(i).address)
soureRNG.copy rngsource(i)
Next i

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
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 = rngSource(i)

would be one guess. rngSource is already a range reference, so it is
specific to the workbook and worksheet to which it refers.

If you want to use the address of that reference to refer to the same
range
in another workbook you could do


Set sourceRNG = Mybook.Range(rngSource(i).address)

--
Regards,
Tom Ogilvy



"Andibevan" wrote in message
...
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)