Copy and paste row into next sheet, into next empty row??

Hi All,

I've got this far, but when the row is cut from the first sheet and pasted into the next it just over rights the last row pasted there instead of pasting the next row down, any help resolving this would be MUCH appreciated. Thanks. Adam.

Private Sub Worksheet_Change(ByVal Target As Range)

 Application.EnableEvents = False
 If Target.Column = 10 And Target.Cells.Count = 1 Then
 Target.EntireRow.Copy Destination:=Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(2, 0)
 lastrow = Range("B65536").End(xlUp).Row + 1
 Cells(lastrow, 2).Select
 
 Target.EntireRow.Delete shift:=xlUp
 Application.EnableEvents = True
 Exit Sub
End If

 If Not Intersect(Target, Range("F:F")) Is Nothing Then
 Target.Offset(0, 1) = Date
 End If
 Application.EnableEvents = True
End Sub

Hi Arlette,

Thanks for the input. It just pastes the row for sheet1 into the same row in sheet2 rather than the next one down (so it compiles a list). I've attached my file. Thank you.

Thank you so much for your time helping me with this. I made the amendment but now the debugger runs in that line?...

Run-time error 1004 "the information cannot be pasted because the copy area and the paste area are not the same size and shape..."

Thank you... Now it's copying it and deleting from sheet1 as it did before but not pasting into sheet2?? I'm thinking of selling my laptop and buying a cat, although that probably won't do as its told either lol

Thanks heaps, that fixed that; but still have the issue with it only pasting in row 3 and not compiling a list (ie row 3 then 4 then 5 etc)in sheet2. Is it doing that okay at your end? I've wondered for a while if my office is buggy as have had macros work fine at work then bug out on my lap top...

Hi -

Try

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo er:
 Application.EnableEvents = False
 If Target.Column = 10 And Target.Cells.Count = 1 Then
 Target.Offset(, -8).Resize(, 9).Copy Destination:=Sheets(2).Cells(Sheets(2).Rows.Count, 2).End(xlUp).Offset(1, 0)
 Target.EntireRow.Delete shift:=xlUp
 Application.EnableEvents = True
 Exit Sub
End If

 If Not Intersect(Target, Range("F3:F300")) Is Nothing Then
 Target.Offset(0, 1) = Date
 End If
 Application.EnableEvents = True
Exit Sub

er:
Application.EnableEvents = True
msgbox err.description
End Sub

Regards,
Event

Thank you Event the macro now runs perfectly. And thank you Arlette for your time too, I was able to sort another buggy macro aswell by using your suggestions (I'm very new to vba, obviously) the cat will have to wait now ... Thanks again. Adam.