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
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.
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.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With