Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Copy and Paste a set range in the next empty row

Tags:

excel

vba

This should be simple but I am having a tough time.. I want to copy the cells A3 through E3, and paste them into the next empty row on a different worksheet. I have used this code before in longer strings of code.. but i had to tweak it and it is not working this time. I get a "application-defined or object-defined error" when i run the code seen below. All help is appreciated.

Private Sub CommandButton1_Click()
Dim lastrow As Long

lastrow = Range("A65536").End(xlUp).row
   Range("A3:E3").Copy Destination:=Sheets("Summary Info").Range("A:A" & lastrow)
End Sub
like image 819
Mike Avatar asked Jul 31 '13 16:07

Mike


People also ask

How do you copy a row and paste it to the next available cell in another sheet?

Copy cells by using Copy and PasteSelect the cell or range of cells. Select Copy or press Ctrl + C. Select Paste or press Ctrl + V.

How do you go to the next blank row in Excel?

If you need to quickly jump to the next blank cell in a row, the Go To feature can help. To do this, press the F5 key, type in the cell address of the first cell in the row, and then press the Enter key. Then, press the Ctrl+Right arrow key on your keyboard. This will take you to the next blank cell in the row.


2 Answers

Be careful with the "Range(...)" without first qualifying a Worksheet because it will use the currently Active worksheet to make the copy from. It's best to fully qualify both sheets. Please give this a shot (please change "Sheet1" with the copy worksheet):

EDIT: edited for pasting values only based on comments below.

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

  Set copySheet = Worksheets("Sheet1")
  Set pasteSheet = Worksheets("Sheet2")

  copySheet.Range("A3:E3").Copy
  pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub
like image 79
Joseph Avatar answered Sep 27 '22 18:09

Joseph


The reason the code isn't working is because lastrow is measured from whatever sheet is currently active, and "A:A500" (or other number) is not a valid range reference.

Private Sub CommandButton1_Click()
    Dim lastrow As Long

    lastrow = Sheets("Summary Info").Range("A65536").End(xlUp).Row    ' or + 1
    Range("A3:E3").Copy Destination:=Sheets("Summary Info").Range("A" & lastrow)
End Sub
like image 21
Andy G Avatar answered Sep 27 '22 19:09

Andy G