Moving entire row to new sheet when date is entered

[Foxy Lady]

Hi there,

I wonder if any one can help me.

I want to move an entire row in spreadsheet A to the next empty line in Spreadsheet 2 if a date(any) is entered into column J.

Can this be done??

Regards

Foxylady

 

[Foxy Lady]

Hi there Peter,

Thank a lot, it is working with the new code, however I found that if I made a change to anything in the row I first need to re-enter the date in Column I to update the row in the new sheet.
If it is the only way then it is fine.
I wish everyone can reply as quick as you with the correct syntax.

 

[kastorma]

Try this: right click the tab of spreadsheet A, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10 And IsDate(Target.Value) Then
    Application.EnableEvents = False
    With Target.EntireRow
        .Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Delete
    End With
End If
End Sub

Press ALT + Q to return to your sheet.

This code works great, but I find it only works at times and I don't understand why. I have tried changing cells format and tried different things but it is still intermittent. The first time I try it in a day works fine, but then nothing helps. Reopening the file either.

Any ideas?

I don't have the requirement to use date specifically, just any text entered in a cell should move whole row to second tab.

 

[VoG]

Try closing then re-opening Excel then use

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10  Then
    Application.EnableEvents = False
    With Target.EntireRow
        .Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Delete
    End With
    Application.EnableEvents = True
End If
End Sub

 

[kastorma]

Try closing then re-opening Excel then use

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10  Then
    Application.EnableEvents = False
    With Target.EntireRow
        .Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Delete
    End With
    Application.EnableEvents = True
End If
End Sub

After some initial issues I think it is working now. Thank you very much!