VBA: Check dropdown list for updates and copy rows, if needed

Question

I would like to create a macro, which is checking column "I:I" for any changes in a dropdown list. This dropdown list contains several entries. Once the entry "Declined" is entered, it should copy the whole row to another worksheet("Declined") and delete the row in the main worksheet("Data"). This would also mean, that this row will always be copied below a previous entry in the worksheet("Declined").

I never worked with Worksheet_Change nor with the Intersect function, so every help is appreciated.

Thanks in advance for your support.

You need `Worksheet_Change` and not `Worksheet_SelectionChange`. [This](https://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) will get you started — Siddharth Rout, Jun 09 '17 at 13:25
Where are you entering "declined" and where is the row? is I:I also in the same "Data" worksheet? show us some data — Gowtham Shiva, Jun 10 '17 at 17:28

score 0 · Accepted Answer · answered Jun 12 '17 at 08:48

I just found a solution, thanks for the ideas:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim shtDeal As String
Dim shtDecl As String
Dim lastrow As Integer

shtDeal = "Name 1"
shtDecl = "Name 2"


If Not Intersect(Target, Range("F1:F10000")) Is Nothing Then

    On Error GoTo ErrHelp

    If Target = "Declined by x" Or _
    Target = "Declined by y" Or _
    Target = "Declined by z" Then

        lastrow = Worksheets(shtDecl).Range("A" & Rows.Count).End(xlUp).Row + 1
        Target.EntireRow.Copy Destination:=Worksheets(shtDecl).Range("A" & lastrow)
        MsgBox "Auf Grund Ihrer Auswahl wurde diese Datenreihe auf das Arbeitsblatt 'Declined' verschoben."
        Target.EntireRow.Delete

    End If

End If

Done:
Exit Sub

ErrHelp:
End Sub

Maybe this could help others with the same problem.

VBA: Check dropdown list for updates and copy rows, if needed

1 Answers1