Move Row from one worksheet to another based on cell value

[danjameswalker]

Firstly; I'm aware this has been asked loads but I just can't figure it out!

I would like to move an row from my Worksheet "Action Log" to another worksheet "Closed Actions" when an "x" is input in the Complete column of my table.

From what I understand I need to use VBA code but I've tried putting a few of the code strings in with no luck.
Each row has some formulas in and some conditional formatting. The Complete column only has conditional formatting in. I don't know if that confuses it

I have never used VBA before so could someone break it down as if youre talking to an infant

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Action Log" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Put an "x" in column K and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("K:K")) Is Nothing Then Exit Sub
    Target.EntireRow.Copy Sheets("Closed Actions").Cells(Sheets("Closed Actions").Rows.Count, "A").End(xlUp).Offset(1)
End Sub

 

[danjameswalker]

Thank you, how do I now get it to delete the row from Action Log but leave it in ClosedActions

 

[mumps]

Try:

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

 

[human688]

I am attempting to use the code you pasted above to apply to my situation, but I must be missing something because it isn't working. I would like to accomplish the same thing - move any given row from the Pending Orders sheet to the Received Orders sheet once column L has any name populated from the drop down as being received. Can you please help?

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Pending Orders" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column L.

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

 

[human688]

Thank you for the speedy reply! Unfortunately though, it did not work.

 

[mumps]

I tested the macro on some dummy data and it worked as you requested. Did you follow the instructions to place the macro in the worksheet code module? Also it is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet.
Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing'
and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[human688]

Ok, so it turns out I just needed to close and reopen excel and it worked! Thank you!! One other question: in addition to having this code, is there a way to include a confirm prompt before it makes the move (after choosing a name from the drop down for the received field) in case of accidental clicking? Or maybe a way to make it move back to the Pending Orders sheet by deleting the name in the received column in the Received Orders sheet?

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("L:L")) Is Nothing Then Exit Sub
    If MsgBox("Are you sure you want to move row " & Target.Row & " to the 'Received Orders' sheet?", vbYesNo) = vbYes Then
        Application.EnableEvents = False
        Target.EntireRow.Copy Sheets("Received Orders").Cells(Sheets("Received Orders").Rows.Count, "A").End(xlUp).Offset(1)
        Target.EntireRow.Delete
        Application.EnableEvents = True
    End If
End Sub