CODES: Move a Row from Sheet

[Charliiee]

Hi,

I have a code to move a row from sheet 1 (On Hand) to sheet 2 (Completed) however when the row is transferred over it replace the one that was previously transferred over to sheet 2 instead of going to the next available row. This is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 10 And Target.Cells.Count = 1 Then
    Target.EntireRow.Copy Destination:=Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    Target.EntireRow.Delete shift:=xlUp
  End If
End Sub

Can someone help me fix it so it put the information in the next available row on sheet 2.

Thanks