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

 

[Joe4]

Welcome to the Board!

If you click the "Debug" button when you get the error, which line of code does it highlight?

 

[IMagill]

Thank you Joe4!

It is highlighting the first line of the second if loop

In this example, I tried changing column N to "New" in the Active sheet

 

[Joe4]

It appears that you are jumping around here.
That code is different than the code you posted in your first post.
Let's not jump around between the different codes in the different sheets yet - let's focus on the one in your original post.

In the context of the code in your original post, can you explain a situation that you do that causes the error (exactly what cell are you updating with what), and what line of code does it highlight if you click debug?

 

[IMagill]

Apologies! I'll work with just the first sheet from the original post.

The run-time error 424 is popping up when I change a value in column N from "New" to "Active"

 

[IMagill]

For reference, here is what that worksheet looks like.

 

[IMagill]

Sorry for the triple post, I meant to say when I change the value in J from New to Active

 

[Joe4]

OK, I see the issue. The problem is all three of your IF blocks are independent. So even if your first condition is met, it is going to check the other two.

However, the issue is you delete the target row at the end of the first block! So it tries to to check the Target range, but it doesn't exist anymore!

You can prevent that from happening by adding an "Exit Sub" row after your delete the row and re-enable events, in each block, i.e.

        Rows(Target.Row).Delete
        Application.EnableEvents = True
        Exit Sub
    End If

Add that line to each block and it should fix your issue.

That should be fine, as with each data update, only one of those three blocks will ever be met/fired.

 

[IMagill]

That worked perfectly! The first two sheets are now functioning.

The last sheet is probably the doozy.

For the last sheet, I need to move rows back to the New or Active sheets when column N is changed to Pending but I need it placed into the New or Active sheet based on the existing value in column J either being New or Active. I'm thinking maybe I need a nested if loop?

The code below will work for flipping a row from the Complete sheet to the New sheet when column N is changed to Pending.. but how would I have it placed into the Active sheet if column J has a value of "Active"?

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 N after row 1 and is set to "Pending"
    If Target.Column = 14 And Target.Row > 1 And Target.Value = "Pending" Then
        Application.EnableEvents = False
'       Copy columns A to N to New sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "N")).Copy Sheets("Acute New 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

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim sh As String

'   Check to see only one cell updated
    If Target.CountLarge > 1 Then Exit Sub
  
'   Check to see if entry is made in column N after row 1 and is set to "Pending"
    If Target.Column = 14 And Target.Row > 1 And Target.Value = "Pending" Then
'       Determine sheet to paste to from column J
        Select Case Cells(Target.Row, "J").Value
            Case "New"
                sh = "Acute New 2025"
            Case "Active"
                sh = "Acute Active 2025"
            Case Else
                MsgBox "Invalid entry in column J"
                Exit Sub
        End Select
        Application.EnableEvents = False
'       Copy columns A to N to New sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "N")).Copy Sheets(sh).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

By the way, please do not use "Quote" tags to post VBA - please use the VBA tags (I tried to go back and update your posts to fix it).
See: How to Post Your VBA Code