Copy and Paste a set range in the next empty row

Question

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

Joseph · Accepted Answer · 2018-10-30T21:21:59.040

14

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

edited Oct 30 '18 at 21:21

answered Jul 31 '13 at 16:58

Joseph

5,070
1
25
26

This copies and pastes correctly, but is not in the next empty row. Thanks for the answer though! – Mike Jul 31 '13 at 17:05
@Mike, gotcha and no problem :) I didn't know how the "Summary Info" sheet was setup, and the code assumes the cells are contiguous from A1 down to the last row in A. If you change `Cells(1,1)` to `Cells([BeginningOfDataInA],1)` it should work for you. – Joseph Jul 31 '13 at 17:18
I'm not sure why it worked the first time.. then when i ran it again it caused an error. – Mike Jul 31 '13 at 17:20
@Mike just edited the answer. Please be sure to change "Sheet1" with the sheet it's copying from. I think this might help. Also, where are you running the code from? Which sheet is active when you run it? – Joseph Jul 31 '13 at 17:33
and this works on copying and pasting, but over writes the information in the first row, instead of going to the next empty row – Mike Jul 31 '13 at 17:36
This is pasting in the correct place.. but since the cells are formulas, I need it to paste the values instead of the formulas.. i'm sorry that this is such a struggle – Mike Jul 31 '13 at 17:52
@Mike, edited the code. How about now? If not, please edit your question to include more details on what you're trying to do. – Joseph Jul 31 '13 at 18:02

score 7 · Answer 2 · answered Jul 31 '13 at 17:48

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

Charles · Answer 3 · 2013-07-31T18:01:31.440

3

You could also try this

Private Sub CommandButton1_Click()

Sheets("Sheet1").Range("A3:E3").Copy

Dim lastrow As Long
lastrow = Range("A65536").End(xlUp).Row

Sheets("Summary Info").Activate
Cells(lastrow + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

edited Jul 31 '13 at 18:01

answered Jul 31 '13 at 17:05

Charles

58
8

This results in a "selection method of range class failed" error on the cell(lastrow + 1,1).select part of the code – Mike Jul 31 '13 at 17:10
I tested it and both answer works for me. I would need more details to understand why it doesn't. – Charles Jul 31 '13 at 17:18
Before i run any of the other macros in the workbook, it works. but when I have data that comes from formulas in the cells i am copying and pasting the code isn't working – Mike Jul 31 '13 at 17:30
See edited answer. Try this it could work if I understood well your problem. – Charles Jul 31 '13 at 17:39
This paste the correct information, but pastes it in the next empty cell is my "YourSheetName" worksheet instead of my "Summary Info" worksheet – Mike Jul 31 '13 at 17:57
I'm not sure to understand exactly what you want, but I edited my solution to take the last empty cell on Sheet1 instead of Summary Info and still paste in Summary Info – Charles Jul 31 '13 at 18:02

score 3 · Answer 4 · edited Dec 11 '14 at 16:33

Below is the code that works well but my values overlap in sheet "Final" everytime the condition of <=11 meets in sheet "Calculator"

I would like you to kindly support me to modify the code so that the cursor should move to next blank cell and values keeps on adding up like a list.

Dim i As Integer
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Calculator")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Final")

For i = 2 To ws1.Range("A65536").End(xlUp).Row

    If ws1.Cells(i, 4) <= 11 Then

        ws2.Cells(i, 1).Value = Left(Worksheets("Calculator").Cells(i, 1).Value, Len(Worksheets("Calculator").Cells(i, 1).Value) - 0)
        ws2.Cells(i, 2) = Application.VLookup(Cells(i, 1), Worksheets("Calculator").Columns("A:D"), 4, False)
        ws2.Cells(i, 3) = Application.VLookup(Cells(i, 1), Worksheets("Calculator").Columns("A:E"), 5, False)
        ws2.Cells(i, 4) = Application.VLookup(Cells(i, 1), Worksheets("Calculator").Columns("A:B"), 2, False)
        ws2.Cells(i, 5) = Application.VLookup(Cells(i, 1), Worksheets("Calculator").Columns("A:C"), 3, False)

    End If
Next i

Copy and Paste a set range in the next empty row

4 Answers4

Linked