VBA Copying a range from one workbook to another one

[Guinaba]

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

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

 

[GWteB]

Do you want to copy from workbook x to y or the other way around?

 

[Fluff]

How about

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:
     With x.Sheets("Total_TPRP").Range("A16").CurrentRegion
         y.Sheets("TPRP_History").Range("A" & DestLastRow + 1).Resize(.Rows.Count, .Columns.Count).Value = .Value
     End With
              
    'close the workbook and after saving
     Workbooks("TPRP History.xlsx").Close SaveChanges:=True
      
 End Sub

 

[Guinaba]

How about

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:
     With x.Sheets("Total_TPRP").Range("A16").CurrentRegion
         y.Sheets("TPRP_History").Range("A" & DestLastRow + 1).Resize(.Rows.Count, .Columns.Count).Value = .Value
     End With
             
    'close the workbook and after saving
     Workbooks("TPRP History.xlsx").Close SaveChanges:=True
     
 End Sub

Thanks @Fluff, but not there yet, your code is taking the header as well when copying from source to target sheet, I tried the code below but still not working

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
Dim RngSource As Range


'## 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:
     
     Set RngSource = x.Sheets("Total_TPRP").Range("A16").CurrentRegion
     Set RngSource = RngSource.Offset(1, 0)
     Set RngSource = RngSource.Resize(RngSource.Rows.Count - 1)
     
     y.Sheets("TPRP_History").Range("A" & DestLastRow).Value = RngSource
   
     'Transfer values from x to y:
     With x.Sheets("Total_TPRP").Range("A16").CurrentRegion
         y.Sheets("TPRP_History").Range("A" & DestLastRow + 1).Resize(.Rows.Count, .Columns.Count).Value = .Value
     End With
               
    'close the workbook and after saving
     Workbooks("TPRP History.xlsx").Close SaveChanges:=True
       
 End Sub

 

[Fluff]

If you don't want the header, try

     'Transfer values from x to y:
     With x.Sheets("Total_TPRP").Range("A16").CurrentRegion.Offset(1)
         y.Sheets("TPRP_History").Range("A" & DestLastRow + 1).Resize(.Rows.Count, .Columns.Count).Value = .Value
     End With

 

[Guinaba]

If you don't want the header, try

     'Transfer values from x to y:
     With x.Sheets("Total_TPRP").Range("A16").CurrentRegion.Offset(1)
         y.Sheets("TPRP_History").Range("A" & DestLastRow + 1).Resize(.Rows.Count, .Columns.Count).Value = .Value
     End With

Thanks @Fluff!!

 

[Fluff]

You're welcome & thanks for the feedback.