Code to copy range between workbooks

[Jlascu]

Hi ... hope anyone can help me. I'm trying to copy a range from one workbook (x.Sheets("Pf")) to another (y.Sheets("ER")).
Using 2 different methods and none work! any pearls of wisdom? greatly appreciate any help!

Thanks!

jlascu

--

Method 1 - Setting Ranges
Set src2Range = Range(Cells(Worksheets("Ctrls").Range("C24").Value + 5, 1), Cells(iNoRecords + 5, Worksheets("Ctrls").Range("C20")))
Set dest2Range = Range(Cells(5, Worksheets("Ctrls").Range("D21").Value), Cells(5, iNoRecords))
y.Sheets("Ctrls").Range(dest2Range.Address).Value = x.Sheets("Pf").src2Range.Value

Error: Run time error 438: Doesn't Support This Property or Method

Method 2 - Direct
y.Sheets("ER").Range(Cells(5, Worksheets("Ctrls").Range("D21").Value), Cells(5, iNoRecords)).Value = _
x.Sheets("Pf").Range(Cells(Worksheets("Ctrls").Range("C24").Value + 5, 1), _
Cells(iNoRecords + 5, Worksheets("Ctrls").Range("C20").Value)).Value

Error: Run time error 1004: Application-defined or object-defined error

 

[JLGWhiz]

For method one, try.

dest2Range = src2Range.Value

in place of

y.Sheets("Ctrls").Range(dest2Range.Address).Value = x.Sheets("Pf").src2Range.Value

 

[Jlascu]

many thanks!! It cleared the error!
however, code just runs thru without pasting data. could there be a step missing in the code to paste?
many thanks again for the help!!

 

[JLGWhiz]

I don't know. Without seeing the worksheet(s) or know where the data resides, it is difficult to analyze that type of problem from the paucity of code provided. My suggestion was to simply use the variables that you set for your source and destination ranges more efficiently than they were being used. If you are not getting values associated with those ranges, perhaps you are using the wrong parameters. To check that, you can use a message box in the code to see if the correct range address is created for your variables. Insert the following after your Set statements.

MsgBox src2Range.Address & vbLf & dest2Range.Address

Then check those ranges to make sure they are what you expected. Also make sure the range is on the right sheet.

 

[JLGWhiz]

Your Set statements as written would put you source range and your destination range on the same worksheet because the Range is not qualified with a parent. Like this:

Set src2Range = x.Sheets("pf").Range(Cells(Worksheets("Ctrls").Range("C24").Value + 5, 1), Cells(iNoRecords + 5, Worksheets("Ctrls").Range("C20")))
Set dest2Range = ySheets("ER").Range(Cells(5, Worksheets("Ctrls").Range("D21").Value), Cells(5, iNoRecords))
destRange = srcRange.Value

 

[Jlascu]

Hi Again,

Seems the error is on the qualifying, below is the entire code.
Many thanks for the help, really appreciate you taking the time!

JL

Code:
[Dim x As Workbook
Dim y As Workbook
Dim src2Range, dest2Range As Range

'Set both workbooks
Set x = Workbooks(sSPVFileName): Set y = Workbooks(sDPMPName) ‘x = data source; y = data

'Pass # records to DPMP
'For d = 1 To Worksheets("Ctrls").Range("C23").Value

Set src2Range = x.Sheets("Pf").Range(Cells(Worksheets("Ctrls").Range("C24").Value + 5, 1), Cells(iNoRecords + 5, Worksheets("Ctrls").Range("C20")))
Set dest2Range = y.Sheets("ER").Range(Cells(5, Worksheets("Ctrls").Range("D21").Value), Cells(5, iNoRecords))
MsgBox src2Range.Address & vbLf & dest2Range.Address

destRange = srcRange.Value

' y.Sheets("ER").Range(Cells(5, Worksheets("Ctrls").Range("D21").Value), Cells(5, iNoRecords)).Value = _
x.Sheets("Pf").Range(Cells(Worksheets("Ctrls").Range("C24").Value + 5, 1), _
Cells(iNoRecords + 5, Worksheets("Ctrls").Range("C20").Value)).Value -à Tried it didn’t work

Next]

 

[JLGWhiz]

Happy to help,
regards, JLG