Dynamic Range to copy between two workbooks

Question

I have problem to make a dynamic range to copy between two workbooks. I have create the following code and when I run step by step the code I take “Run time error 1004” “Method Range of object worksheet failed” My thought is to create dynamic range for the workbook with new data because is change all the time and the only last cell with data is in column “D” then expand this to column “S” and copy this to Master workbook Data sheet and again find the last used cell in column D and offset this to column “A” . How can make this task?

Sub CopyValuesToMaster()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet

Dim RngAC1 As Range
Dim RngAC2 As Range
Dim NewRng As Range
Dim DestLastRow As Long

Set wsCopy = Workbooks("sl0032019.xls").Worksheets("Sheet1")
Set wsDest = Workbooks("Master-Braun.xlsx").Worksheets("Data")

DestLastRow = Cells(Rows.Count, "D").End(xlUp).Offset(1, -3).Row
CopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "D").End(xlUp).Row

Set RngAC1 = wsCopy.Range("A1")
Set RngAC2 = wsCopy.Range(Cells(Rows.Count, "D").End(xlUp).Offset(0, 15).Row)
Set NewRng = Range(RngAC1.Address & ":" & RngAC2.Address)

NewRng.Copy wsDest.Range("A" & DestLastRow)

End Sub

(1) Which line errors? (2) You should add sheet reference on destlastrow line (3) What range is rngac2 supposed to be defining, that doesn't look right? — SJR
Hi SJR in this line with RngAC2 i take the error... i am trying to create a renge from A1 to last cell S column but the only way to find the address is to look in column D that i have the last cell filled and expand to column S — Vaggelis

SJR SJR · Accepted Answer · 2019-03-04T11:04:11

Try this.

Sub CopyValuesToMaster()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet

Dim RngAC1 As Range
Dim RngAC2 As Range
Dim NewRng As Range
Dim DestLastRow As Long

Set wsCopy = Workbooks("sl0032019.xls").Worksheets("Sheet1")
Set wsDest = Workbooks("Master-Braun.xlsx").Worksheets("Data")

DestLastRow = wsDest.Cells(Rows.Count, "D").End(xlUp).Offset(1, -3).Row
CopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "D").End(xlUp).Row

With wsCopy
   Set RngAC1 = .Range("A1")
   Set RngAC2 = .Range("S" & .Cells(.Rows.Count, "D").End(xlUp).Row)
End With

Range(RngAC1, RngAC2).Copy wsDest.Range("A" & DestLastRow)

End Sub

Dynamic Range to copy between two workbooks

1 Answers