copy data from Sheet1 to sheet2 and Paste Data Below the Last Used Row

[Patriot2879]

Hi good morning, i have the ode below where I am trying to copy all the data in a sheet called 'New' and paste into the last row in a sheet called 'Combined but its not working for me can you help please.

Private Sub CommandButton3_Click()
Application.ScreenUpdating = False
  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet

  Set copySheet = Worksheets("New")
  Set pasteSheet = Worksheets("Combined")
  copySheet.Range("A:BE").Copy
  pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  End Sub

 

[Darren Bartrup]

I take it you're getting a Copy area and paste area aren't the same size. Range("A:BE") are whole columns. Only copy the required range - rows 1:1048576 won't paste into rows 1000:1048576 manually or via VBA.

 

[Patriot2879]

Hi sorry I don't know what you mean, I am still new to this, I just want to copy and paste to last used row. and to be all the same size etc. hope you can help

 

[Darren Bartrup]

Something like:

Private Sub Test()

  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet
  Set copySheet = Worksheets("New")
  Set pasteSheet = Worksheets("Combined")
  
'Only copy the required range.  
With copySheet
    .Range(.Cells(1, 1), .Cells(Rows.Count, "BE").End(xlUp)).Copy
  End With
  
  pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

End Sub

 

[Patriot2879]

Hi thank you for your help this works but only copies the top line, how do I copy all the data from A2:BE in 'New' to last used row into other sheet? thanks again

 

[vcoolio]

Hello Patriot,

Try the code amended as follows:-

Private Sub CommandButton3_Click()

   Dim copySheet As Worksheet
   Dim pasteSheet As Worksheet
   Set copySheet = Worksheets("New")
   Set pasteSheet = Worksheets("Combined")
  
Application.ScreenUpdating = False

With ws
    .Range(.Cells([COLOR=#ff0000]2[/COLOR], 1), .Cells(Rows.Count, "BE").End(xlUp)).Copy
    ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End With
 
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

.......or try:-

Private Sub CommandButton3_Click()
 
Application.ScreenUpdating = False

          Dim ws As Worksheet: Set ws = Sheets("New")
          Dim ws1 As Worksheet: Set ws1 = Sheets("Combined")
          Dim lr As Long: lr = ws.Range("A" & Rows.Count).End(xlUp).Row
        
          ws.Range("A2:BE" & lr).Copy
          ws1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
          
Application.CutCopyMode = False
Application.ScreenUpdating = True
  
End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[Darren Bartrup]

Try

With copySheet
    .Range(.Cells(2, "A"), .Cells(.Cells(Rows.Count, "A").End(xlUp).Row, "BE")).Copy
  End With

Change '.Cells(Rows.Count, "A")' to a column that has data on the last row to copy.

 

[Patriot2879]

thanks that's amazing please can you advise where I went wrong? just for my training knowledge

 

[Darren Bartrup]

You tried to copy the whole column which is 1048576 rows and paste further down the sheet.

For example; if you copy A1:A1048576 you're copying 1048576 rows. If you try and paste that into row 11 you'll only have 1048566 rows available to paste into - it won't fit.

 

[Patriot2879]

ok thankyou is that because I didn't name the .Range correctly?