VBA Automatic Row Movement With Several Criteria

[IMagill]

Good Afternoon!

I am attempting to write code for an excel workbook that will automatically move a row from one sheet to another based on the selection in a drop down menu in the row. I'm having some trouble and I'm not sure what the correct way to code this is.

Row 1 is all of my headers
Column A is the start of all data through Column N
The two columns that will determine where the row moves are J and N
There are three sheets named "Acute New 2025" "Acute Active 2025" and "Acute Complete 2025"

In the Acute New 2025 sheet, I need the coding to:
Move a row to the Acute Active 2025 sheet if column J is changed to "Active"
Move a row to the Acute Complete 2025 sheet if column N is changed to "Accept" or "Decline"

In the Acute Active 2025 sheet, I need the coding to:
Move a row to the Acute New 2025 sheet if column J is changed to "New"
Move a row to the Acute Complete 2025 sheet if column N is changed to "Accept" or "Decline"

In the Acute Complete 2025 sheet, I need the coding to:
Move a row to the Acute New 2025 sheet if column N is changed to Pending and column J is equal to "New"
Move a row to the Acute Active 2025 sheet if column N is changed to Pending and column J is equal to "Active"

Below is my code for the Acute New 2025 sheet. I am running into Run-time error 424. If I can get this working, I'm sure I can figure it out for the Active sheet. I have no idea how to approach the Complete sheet as I'm not sure how to run the if loop to check column J when the target is column N on change.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see only one cell updated
    If Target.CountLarge > 1 Then Exit Sub
  
'   Check to see if entry is made in column J after row 1 and is set to "Active"
    If Target.Column = 10 And Target.Row > 1 And Target.Value = "Active" Then
        Application.EnableEvents = False
'       Copy columns A to N to Active sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "N")).Copy Sheets("Acute Active 2025").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
  
'   Check to see if entry is made in column N after row 1 and is set to "Accept"
    If Target.Column = 14 And Target.Row > 1 And Target.Value = "Accept" Then
        Application.EnableEvents = False
'       Copy columns A to N to Complete sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "N")).Copy Sheets("Acute Complete 2025").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
  
'   Check to see if entry is made in column N after row 1 and is set to "Decline"
    If Target.Column = 14 And Target.Row > 1 And Target.Value = "Decline" Then
        Application.EnableEvents = False
'       Copy columns A to N to Complete sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "N")).Copy Sheets("Acute Complete 2025").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
  
End Sub

 

[IMagill]

You have saved me so much headache. Thank you so much for all that you do, everything is working perfectly.

 

[Joe4]

You are welcome.
Glad I was able to help!

I hope what I did with the last code makes sense. LMK if you have any questions about it.