Copy and Paste - Specific cells and range

[jamescooper]

Trying to copy a range to a specific range referencing cells, anyone know why this will work please?

Range("AG2:BC" & Range("BI1")).Copy _
Destination:=Sheets("Data").Range("D" & .Range("AD3"), .Range("Z" & .Range("AD4")))

 

[MARK858]

Do you have a With statement that you aren't showing? and what are in cells AD3 and AD4?

 

[offthelip]

I am not sure what you are trying to do but this code does at least compile and should show you what you need to do to fix your code

ii = ActiveSheet.Range("B1:B1")
With Worksheets("sheet3")


id3 = .Range("AD3")
id4 = .Range("AD4")


Range("A2:B" & ii).Copy Destination:=Sheets("Data").Range("D" & id3): .Range ("Z" & id4)
End With

 

[jamescooper]

Do you have a With statement that you aren't showing? and what are in cells AD3 and AD4?

No with statement

Its in a Worksheet_Calculate, AD3 is 9 and AD4 is 16 as they are variable.

Private Sub Worksheet_Calculate()
Range("AG2:BC" & Range("BI1")).Copy _
Destination:=Sheets("Data").Range("D" & .Range("AD3"), .Range("Z" & .Range("AD4")))
End Sub

 

[jamescooper]

Works but gives me run-time error 438 after copying? Thanks.

 

[MARK858]

Well the way you have written it .Range("AD3") and .Range("Z" & .Range("AD4") would need a With statement to go with the period/fullstop in front of Range.
What sheet are the 2 cells on?

 

[jamescooper]

The two cells are on sheet ("Data")

 

[MARK858]

Then either

Private Sub Worksheet_Calculate()
    With Sheets("Data")
        Range("AG2:BC" & Range("BI1")).Copy _
                Destination:=.Range("D" & .Range("AD3"), .Range("Z" & .Range("AD4")))
    End With
End Sub

or

Private Sub Worksheet_Calculate()
    Range("AG2:BC" & Range("BI1")).Copy _
            Destination:=Sheets("Data").Range("D" & Sheets("Data").Range("AD3"), Sheets("Data").Range("Z" & Sheets("Data").Range("AD4")))
End Sub

and if you want you can remove the

Destination:=

if you want.

 

[jamescooper]

Thanks!