Excel VBA - Copy range from one sheet to another, next empty row

Question

I'm trying to take a range from one sheet and copy it to the next empty row in another sheet (basically, paste into the range A12:D12 for next empty row in the other sheet). The range will never change. I've seen a lot of questions like this, with people saying the answers work great, but I can't get it to work.

Very new to VBA. Here is the code I'm using:

Private Sub CommandButton1_Click()

    Dim NextRow As Range
    Set NextRow = Range("A" & Sheets("Sheet3").UsedRange.Rows.Count + 1)  

    Sheet1.Range("A12:D12").Copy
    Sheet3.Activate
    NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
    Application.CutCopyMode = False

    Set NextRow = Nothing

End Sub

This runs but it doesn't actually paste any values into Sheet3. Is there something I'm missing? Is there a better way to do this?

Thanks!

Answer 1

You just had an issue in the second line defining NextRow.
Is there a better way to do this? It depends on your needs; personally I do not like to activate/select other cells during a VBA macro so e.g. I would get rid of the Sheet3.Activate. I would also copy the stuff 'manually' without using the clipboard to avoid changing the user's clipboard contents.

Private Sub CommandButton1_Click()

    Dim NextRow As Range
    Set NextRow = Sheet3.Range("A" & Sheet3.Rows.Count).End(xlUp).Offset(1, 0)

    Sheet1.Range("A12:D12").Copy
    Sheet3.Activate
    NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
    Application.CutCopyMode = False

    Set NextRow = Nothing

End Sub