Moving entire row based on value from drop down list using VBA

[RAM_82]

Dear readers,

I know this is a common question as I have read other threads related to this issue. But it seems I can't apply the solutions from the other treads - probably due to my lack of experience with VBA. So I hope someone is able te guide me in the right direction

I use to keep track of my work tasks. When I get a new task I add it to the spreadsheet and I assign it a status (column M = dropdown list). What I would like to do is to enable a macro that moves the entire row (when a selection has been made from the dropdown list) to the corresponding sheet.

Example. Value dropdown menu is "done", then the entire row should be moved (not copied) towards sheet "Done".

I've tried various solutions (see below) without getting there entirely

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
If Intersect(Target, Range("M:M")) Is Nothing Then Exit Sub
If Target = "Done" Then
Target.EntireRow.Copy Sheets("Done").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Target.EntireRow.Delete
End If
Application.ScreenUpdating = True
End Sub


I'm looking forward to read your advice(s).

 

[Fluff]

Add this line

Application.EnableEvents = True

just before the End Sub.
Then run

Sub chk()
Application.EnableEvents = True
End Sub

And try your code again.

 

[Joe4]

You are not re-enabling events. So it would never work more than once per session.
You need to make sure to re-enable it, any way you are exiting your code.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    If Intersect(Target, Range("M:M")) Is Nothing Then GoTo exit_sub
    
    If Target = "Done" Then
        Target.EntireRow.Copy Sheets("Done").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete
    End If

exit_sub:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

Note that your code is probably currently disabled. So you may need to turn it back on by manually running this one line code first:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

 

[Joe4]

Fluff,

You actually need to do a little more than that, to address this part of the code which exits immediately, thus never getting to the bottom where you are adding the new line:

[COLOR=#333333]Application.EnableEvents = False[/COLOR]
[COLOR=#ff0000]If Intersect(Target, Range("M:M")) Is Nothing Then Exit Sub[/COLOR]

I addressed it in my version in the previous post.

 

[Fluff]

Agreed.
I'm so used to putting the EnableEvents line after the Intersect line that I forgot about that.

 

[Joe4]

Agreed.
I'm so used to putting the EnableEvents line after the Intersect line that I forgot about that.

To be honest, I did the same thing you did initially, and then performed a few tests, and wondered why it stopped working.
Then it jumped out at me.

That is a good practice though. I often put the disable events line just before my change when writing it myself (and not editing someone else's).