Automatically cut a row to another worksheet based on cell value

[Jackson68]

Hi,
I have a worksheet "Holidaymakers" that contains a list of current employees. Column "I" is for termination date, and when I enter a date in here I would like the entire row to be cut and pasted into the worksheet "Past Holidaymakers". The row spans from "A:X".

There was a previous posting asking a very similar question, but when I used the macro provided in that posting I received the same error message VB error "400"?

I have attached a test/sample version of the workbook.

Any help would be appreciated as my VB skills are limited.

 

[Marc L]

Hi !​

Column "I" is for termination date

Nope ! Check and revert …​

​

 

[Jackson68]

Hi !​

Nope ! Check and revert …​

​

Typo, Should read Column "H"

 

[Fluff13]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("H5:H100")) Is Nothing Then
      Application.EnableEvents = False
      Rows(Target.Row).Copy Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1)
      Rows(Target.Row).Delete
      Application.EnableEvents = True
   End If
End Sub

This needs to go in the "Holidaymakers" sheet module