Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need macros for copy data into new workbooks | Excel Discussion (Misc queries) | |||
Copy/ move selected data from workbooks to seperate worksheets or workbooks | Excel Worksheet Functions | |||
Can't copy data from cells between workbooks within the same excel | Excel Discussion (Misc queries) | |||
Copy data across workbooks | New Users to Excel | |||
text search between workbooks, copy data | Excel Programming |