Macro - Paste into next empty row

[stusam]

Hi.

I'm looking for advice on how to update my simple macro. I would like it to copy selected data from Sheet 1, and paste into the next empty row in Sheet 2, before deleting it from sheet 1.

Here's what I have so far:

Range("B2:F2").Select
Selection.Copy
Sheets("Sheet 2").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet 1").Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub

Is anyone able to help explain what instruction is needed to ensure the data is pasted into the next empty row?

Thank you.

 

[mumps]

Try:

Sub CopyRange()
    With Sheets("Sheet1")
        .Range("B2:F2").Copy Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1)
        .Range("B2:F2").ClearContents
    End With
End Sub

 

[stusam]

Try:

Sub CopyRange()
    With Sheets("Sheet1")
        .Range("B2:F2").Copy Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1)
        .Range("B2:F2").ClearContents
    End With
End Sub

Thank you for such a quick response. I tried the code but I'm getting am "Out of range" error. The debugger points to the "With Sheets(Sheet1")" line as causing the issue. This is my first time dabbling with VBA, so it's almost certainly something I'm doing wrong. Here's a simplified version of my file with my original code. If you are able to look at it for me, I'd be most grateful.

 

[mumps]

Oops!! Put a space between "Sheet" and the number in the code.
Sheet 1
Sheet 2

 

[stusam]

Oops!! Put a space between "Sheet" and the number in the code.
Sheet 1
Sheet 2

Perfect, thank you! A good lesson learned for me,

 

[mumps]

You are very welcome.