VBA to cut and paste a row into another sheet if one of the cells meets criteria.

[TJKnight]

I run a warehouse and we are getting ready to ramp up the volume of orders we receive from our customer. I want the sheet to cut the entire row and paste it into the next sheet anytime the dropdown box in column "I" is changed to yes.

 

[MK0010]

Hello,

Sorry i should have been clearer! If possible I would like the information from Customer 1,2,3 to transfer from 'Live jobs' to the customer tab it relates to on the bottom using a key word like 'yes' or 'complete' in column H and this would then make it cut from live jobs and paste in the relevant tab. The initial code you had put up worked perfectly but only for one customer so i wonder if there is a way to extend it? Hopefully the attached image may make it clearer?
Thanks in advance for any help

 

[Joe4]

Can you insert a blank row between the header and the first customer name? Doing so would make it the same so there is a blank row before each customer, allowing us to use that fact in our code.
Assuming that your sheets are named EXACTLY the same as the Customer names shown in blue, this code should do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lr As Long
    Dim cust As String
    
'   Exit if more than one cell updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if column is H (8th column) and value is "Yes"
    If (Target.Column = 8) And (Target.Value = "Yes") Then
'       Get customer name to know which sheet to apply to
        cust = Cells(Target.Row, "A").End(xlUp).Value
'       Find first blank row on new sheet
        lr = Sheets(cust).Cells(Rows.Count, "A").End(xlUp).Row + 1
'       Copy to new sheet
        Application.EnableEvents = False
        Rows(Target.Row).Copy Sheets(cust).Cells(lr, "A")
'       Delete old row
        Rows(Target.Row).Delete
'       Reenable events
        Application.EnableEvents = True
    End If

End Sub

 

[MK0010]

Hello,

that doesn't seem to work? I probably just need to be transparent with the data i need sorting so attached is the sheet i work from (I've had to shrink it down to get it all in) will this make a difference to the code?

 

[Joe4]

You switched columns on me!
In your last post and example, you said it was column H being updated, but in your latest example, it looks like it is actually column I.

If that is the case, you need to change this:

'   Check to see if column is H (8th column) and value is "Yes"
    If (Target.Column = 8) And (Target.Value = "Yes") Then

to this:

'   Check to see if column is I (9th column) and value is "Yes"
    If (Target.Column = 9) And (Target.Value = "Yes") Then

I typically advise people, show us your data structure, EXACTLY as you have it.
Don't try to oversimplify, unless you are REAL comfortable modifying VBA code to make it work for your actual structure.

 

[MK0010]

That's working perfectly now!
Thank you so much for your help and patience, you're amazing

 

[Joe4]

You are welcome.
Glad I was able to help.