Guinaba
Board Regular
- Joined
- Sep 19, 2018
- Messages
- 218
- Office Version
- 2016
- Platform
- Windows
Hi guys,
Trying to copy a range from one workbook to another one using the code below, but not sure what is wrong with the line:
.Range("A" & DestLastRow).Resize(.Rows.Count, .Columns.Count) = .Value
Trying to copy a range from one workbook to another one using the code below, but not sure what is wrong with the line:
.Range("A" & DestLastRow).Resize(.Rows.Count, .Columns.Count) = .Value
VBA Code:
Sub TPRP_Copy()
'Find the last used row in both sheets and copy and paste data below existing data.
Dim x As Workbook
Dim y As Workbook
Dim DestLastRow As Long
'## Open both workbooks first:
Set x = Workbooks.Open("S:\Everyone\Demand Planning\Primary & VE\Wow Daily Tracker\Source.xlsm")
Set y = Workbooks.Open("S:\Everyone\Demand Planning\Primary & VE\Wow Daily Tracker\TPRP History.xlsx")
With Workbooks("TPRP History.xlsx").Worksheets("TPRP_History")
DestLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
'Transfer values from x to y:
y.Sheets("TPRP_History").Range("A" & DestLastRow).Value = x.Sheets("Total_TPRP").Range("A16").CurrentRegion
With Workbooks("TPRP History.xlsx").Worksheets("TPRP_History")
.Range("A" & DestLastRow).Resize(.Rows.Count, .Columns.Count) = .Value
End With
'close the workbook and after saving
Workbooks("TPRP History.xlsx").Close SaveChanges:=True
End Sub