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.

 

[Joe4]

Welcome to the Board!

What is the name of the sheet you want to paste this row too?

 

[TJKnight]

Welcome to the Board!

What is the name of the sheet you want to paste this row too?

The name of the sheet will be "QA"

 

[Joe4]

OK, right-click on the sheet tab name at the bottom of the screen, select "View Code", and then paste this code in the resulting VB Editor window that pops up.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ows As Worksheet
    Dim nws As Worksheet
    Dim lr As Long
    
'   Set name of worksheet to copy to, and name of current sheet
    Set nws = Sheets("QA")
    Set ows = ActiveSheet
    
'   Exit if more than one cell updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if row > 1 and value is "Yes"
    If (Target.Row > 1) And (Target.Value = "Yes") Then
'       Find first blank row on new sheet
        lr = nws.Cells(Rows.Count, "A").End(xlUp).Row + 1
'       Copy to new sheet
        Application.EnableEvents = False
        ows.Rows(Target.Row).Copy nws.Cells(lr, "A")
'       Delete old row
        ows.Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If

End Sub

This should do what you want automatically upon changing the value in column I.

 

[TJKnight]

Thank you so much!!! Now if I want it to move out of the QA sheet in to the Ready to ship sheet do I use the same code except for the "Set nws = sheets ("QA") part?

 

[Joe4]

Now if I want it to move out of the QA sheet in to the Ready to ship sheet do I use the same code except for the "Set nws = sheets ("QA") part?

Yes, but make sure that you put the code in the correct place. It will not go in the same place as the first code, but rather in the "QA" sheet module in VBA.
(In order for these event procedures to work automatically, they must be placed in the proper sheet modules, and must be named a certain way).

 

[TJKnight]

You have been an enormous help! Thank you so much!

 

[Joe4]

You are most welcome.
Glad I was able to help!

 

[MK0010]

Hello,

I am doing some work on excel and the above information was brilliant, i do have a query to take it one step further though.
If i was using a front sheet for what i will call "live jobs" and i wanted the function above but for it to apply to multiple tabs is there a way to do this? So when 'yes' its typed in Column I it went to various other tabs (i.e customer 1, customer 2, Customer 3) depending on where they were sitting in the spreadsheet?

 

[Joe4]

Welcome to the Board!

You would have to explain this part in more detail:

So when 'yes' its typed in Column I it went to various other tabs (i.e customer 1, customer 2, Customer 3) depending on where they were sitting in the spreadsheet?

in order that we have know how to determine which sheet to paste it to.

Posting a sample of how your sheet and the data is structured would probably be helpful here.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.