Move rows based on 2 different values

[kanderson83]

I am trying to have rows automatically move to different sheets based on 2 different values. I want the row to move once "resolved" but I want it to move to a specific sheet based on the partner. I am currently using this code below to have the rows move automatically from the "currently working" sheet to the "resolved " sheet. Instead of moving to resolved, I would like them to move to a sheet based on the partner name in column "C."

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Application.EnableEvents = False
    'If Cell that is edited is in column G and the value is resolved then
    If Target.Column = 7 And Target.Value = "Resolved" Then
        'Define last row on resolved worksheet to know where to place the row of data
        LrowResolved = Sheets("Resolved").Cells(Rows.Count, "A").End(xlUp).Row
        'Copy and paste data
        Range("A" & Target.Row & ":H" & Target.Row).Copy Sheets("Resolved").Range("A" & LrowResolved + 1)
        'Delete Row from Project List
        Range("A" & Target.Row & ":H" & Target.Row).Delete xlShiftUp
    End If
    Application.EnableEvents = True
End Sub

 

[Juddman79]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 4 Then
      If LCase(Target.Value) = "accepted" Then
         With Target.EntireRow
            .Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            .Delete
         End With
      ElseIf LCase(Target.Value) = "rejected" Then
         With Target.EntireRow
            .Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            .Delete
         End With
      End If
   End If
End Sub

The code is case insensitive, so it doesn't matter how it's entered into the sheet.

Thank you for your very prompt response, it works brilliantly. I was going wrong by not having 'Elseif' in line 9 of the code.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Juddman79]

You're welcome & thanks for the feedback

No problem, I appreciate the support more knowledgeable people (like yourself) pass on to a complete novice like me. If I wanted to also move columns marked as "withdrawn" as well as 'rejected' to the same sheet (Sheet3) can I just add to the first line of the block of code below or would I have to copy the complete bit of code below and add it to the bottom and change the Target.Value to "withdrawn".

      ElseIf LCase(Target.Value) = "rejected" Then
         With Target.EntireRow
            .Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            .Delete
         End With

 

[Fluff]

You can just add that to the ElseIf line

      ElseIf LCase(Target.Value) = "rejected" Or LCase(Target.Value) = "withdrawn" Then

 

[Juddman79]

You can just add that to the ElseIf line

      ElseIf LCase(Target.Value) = "rejected" Or LCase(Target.Value) = "withdrawn" Then

That's brilliant thank you, it works a treat.

 

[Fluff]

My pleasure & thanks for the feedback

 

[ShandaD]

Hello, I am new here and haven't really worked with VBA in the last 15 years solid. Could you please help me with this exact scenario, except
to simply move the lines to the bottom of the current sheet?

 

[Fluff]

Please start a thread of your own, giving full details of what you want.
Thanks