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

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,259
Office Version
  1. 2010
Platform
  1. Windows
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.

Code:
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.
 
Upvote 0
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
 
Upvote 0
Something like:

Code:
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
 
Upvote 0
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
 
Last edited:
Upvote 0
Hello Patriot,

Try the code amended as follows:-

Code:
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:-

Code:
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.
 
Last edited:
Upvote 0
Try

Code:
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.
 
Upvote 0
thanks that's amazing :) please can you advise where I went wrong? just for my training knowledge :)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,907
Messages
6,175,300
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top