Cut (Not Copy)and Paste row from one sheet to another based on a drop down value in a cell

[jpdtubaman]

Hello. I am new to this Forum, but I am very impressed with the information that is in it. I do have a question that I could not easily find the answer to.

I have a sheet that tracks purchases called "PC". I also have an sheet that archives completed purchases called "Archive - PC". In Column B, there is a drop-down status field. When Column B is changed to "Completed" I want excel to automatically move(not copy) the row from "PC" to "Archive - PC". I also have data validation set up for the 1st 30 rows of "PC" so when the row is moved I want the new row 30 to have the same data validation. Can someone assist me? Thank you.

[AB33]

I have not tested it, but try it by copying this code in to PC sheet

Please Login or Register  to view this content.

[jpdtubaman]

Thank you AB33. Unfortunately, it looks like there is a type mismatch with the If Target.Column = "Completed". I think I need the value of the cell and not the number of the column. Any ideas?

[AB33]

Sorry! You are right!

If Target.Value = "Completed" Then

[jpdtubaman]

YAYAYAYAYAY!!! It worked. Thank you very much.

[AB33]

I do not think deleting the entire row deletes the drop down lists as these are not part of cell, rather part of a cell validation.
If you are happy, please close this thread. Go to the top-right-hand side of this page, select, "Solved" from the "Threads Tools" drop down menu.