VBA Code for automatically moving a row to different sheets based on multiple dropdown list

[marz25]

Hi,

I've been able to successfully move rows from one sheet to another based on a cell value in a row using VBA code, but now I can't figure out how to do the same for multiple cell values and different sheets.
This is what I'm trying to do-
If dropdown list selection in Column R after Row 5 = "Active", then move row data from column B to R to destination sheet "Active List" column B after row 4.
if dropdown list selection in Column R after Row 5 = "Inactive", then move row data from column B to R to destination sheet "Inactive List" column B after row 4.

Can you help me? I'm a novice.

 

[myall_blues]

What should happen to the data in the 'leftover' columns if there is any? For example, in columns A and S onwards?

Anyway, something along these lines probably, where your sheet with the dropdowns is called "Data"

Sub MoveRow()
    Dim lastRow As Long, i As Long, activeCount As Long, inactiveCount As Long
    Dim dataArr As Variant
    Dim wsdata As Worksheet, wsactive As Worksheet, wsinactive As Worksheet
    Set wsdata = ThisWorkbook.Worksheets("Data")
    Set wsactive = ThisWorkbook.Worksheets("Active list")
    Set wsinactive = ThisWorkbook.Worksheets("Inactive list")
    wsdata.Activate
    lastRow = wsdata.UsedRange.Rows.Count + 4
    activeCount = 4
    inactiveCount = 4
    For i = 6 To lastRow
        Select Case Range("R" & i).Value
            Case "Active"
                activeCount = activeCount + 1
                wsactive.Range("B" & activeCount).Resize(1, 17).Value = wsdata.Range("B" & i & ":R" & i).Value
                wsdata.Range("B" & i & ":R" & i).ClearContents
            Case "Inactive"
                inactiveCount = inactiveCount + 1
                wsinactive.Range("B" & inactiveCount).Resize(1, 17).Value = wsdata.Range("B" & i & ":R" & i).Value
                wsdata.Range("B" & i & ":R" & i).ClearContents
            Case Else
                'do nothing
        End Select
    Next i
End Sub