Move rows to another sheet when value is chosen from a drop-down menu in cell

[Oweineh]

Hi Dear Excelforum users this is my first post, please be kind to me

This is my first time using VBA so i am a complete noob, i am making a simple complaint tracking system and there is a column where there are two choices Resolved and Unresolved
if you choose resolved it copies the row to the resolved sheet and if it's unresolved it copies the row to the unresolved sheet. i attached a template Thank you

EDIT: sorry i forgot to mention that i don't want the status column which contains the choices to be copied to the other sheets, also the unresolved sheet doesn't have a 'date closed' column because the complaint is not yet resolved so i dont want it to be copied there, i want it only in the resolved sheet. Sorry if i suck at explaining the issue i think seeing the file will help explain better

[alansidman]

Do you want the complaint moved (cut from sheet) or copied?

[Oweineh]

Thanks for the quick reply , i just want to copy the row not move it

[Oweineh]

Sorry for bumping up the thread, but i heard that an advanced filter can help me is that true? i don't want to just filter the complaints i want them copied from the main sheet .

[alansidman]

Try this:

Sub Foo()
    Dim w1 As Worksheet
    Dim w2 As Worksheet
    Dim w3 As Worksheet
    Set w1 = Sheets("Complaints")
    Set w2 = Sheets("Resolved")
    Set w3 = Sheets("Unresolved")
    Dim lr As Long
    lr = w1.Range("A" & Rows.Count).End(xlUp).Row
    Dim lrr As Long
    Dim lru As Long
    Dim i As Long
    
    Application.ScreenUpdating = False
        For i = 7 To lr
        lrr = w2.Range("A" & Rows.Count).End(xlUp).Row
        lru = w3.Range("A" & Rows.Count).End(xlUp).Row
            If w1.Range("I" & i) = "Resolved" Then
                w1.Range("I" & i).EntireRow.Copy w2.Range("A" & lrr + 1)
                Else
            If w1.Range("I" & i) = "Unresolved" Then
                w1.Range("I" & i).EntireRow.Copy w3.Range("A" & lrr + 1)
            End If
            End If
        Next i
    Application.ScreenUpdating = True
    MsgBox ("Records Moved")
End Sub

By the way, you have a typo in one of the sheet names. Correct it before you run the code or the code will fail.