• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatically cut a row to another worksheet based on cell value

Jackson68

New Member
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.
 

Attachments

Fluff13

Active Member
How about
Code:
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
 
Top