Finding next blank cell error

[ahowardmiller]

Everything in my code works perfectly except the line marked below. Can someone help me figure out how to get it to find the next blank row and then paste. If I take the line of code out, it works but it replaces the previously saved data.

Private Sub CommandButton1_Click()
    With ActiveCell
    Range(Cells(.Row, "A"), Cells(.Row, "N")).Select
End With


    Selection.Copy
    Sheets("Completed").Select
[COLOR=#ff0000]    Cells(Rows.Count, "A").End(xlUp)(2).Select ** I get a range failed error 1004 here[/COLOR].
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
End Sub

Thank you for your time,
Aaron

 

[D3allamerican07]

Aaron,

Try:

    nextRow = Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A" & nextRow).Select

This identifies the last row + 1, then selects it in the next line. Let me know if this is what you were looking for.

Bill

 

[ahowardmiller]

Bill:
I've tried your code and similar code. I get the same range error where it gets to range. I'm stumped because I've used similar code in multiple projects. Thanks for your help.

Aaron

 

[Fluff]

Hi, welcome to the board.
As an alternatively, try

Private Sub CommandButton1_Click()
    With ActiveCell
    Range(Cells(.Row, "A"), Cells(.Row, "N")).Select
End With


    Selection.copy Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
End Sub

Added
Having seen post#3 do you have sheet protection?

 

[D3allamerican07]

Aaron,

I believe its a target error then:

Try:

nextRow = Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Completed").Range("A" & nextRow).Select

Let me know if it's any luck.

Bill

 

[ahowardmiller]

Bill:
It must have been. The code you provide worked perfectly. Thank you for all your help.

Aaron

 

[D3allamerican07]

Aaron,

That's great, no problem!

Bill