Code to search for items, copy to different sheet and delete them

Hi all,

I have a workbook with one sheet (called "Pipeline") and another sheet called "Completed". The Pipeline sheet is used to keep track of all of the tasks that the team are working on, and then when the task is completed, it needs to be moved to the "Completed tab".

I currently have managed to write some code that looks for items marked "completed" in row G of the "Pipeline" sheet, then copies them over to the "Completed" tab, and then deletes the row from the "Pipeline" sheet.

There is one macro to copy the completed cells (called "Transfer"), and then another to delete the cells (called "Delete"). I then just have macro called "Clean" that just calls the transfer macro, and then calls the delete macro.

However, I'm new to VBA and I'm pretty sure the code is awful as it sometimes just doesn't work. Often, there are 6 tasks marked complete, and only some of them will be copied over, but all of them will be deleted (a disaster).

I'm just hoping if anyone could give me some tips to make the code better and more robust? Thanks!

Transfer Macro:

Please Login or Register  to view this content.

Delete Macro:

Please Login or Register  to view this content.

Overall Macro:

Please Login or Register  to view this content.

Thanks so much for your response! Unfortunately it doesn't seem to work - nothing happens when I select col G as completed from the dropdown. The only other code in the spreadsheet is in Sheet 1 (Pipeline), which ensures that the team cannot select completed as an option without providing a completed date in column U. Does this interfere with anything?

Please Login or Register  to view this content.

Thanks - I actually was being silly and didn't paste into the right worksheet module. I have spent some time and merged your code with my other code in post #3 and have got the below!

One last question I had - with this code I cannot copy or delete whole rows - I get the error message "The status may not be set to complete when no completion date has been provided in Column U", and the cells are just end up blank.

I have a feeling it's something to do with the Application.EnableEvents but not sure how to correct it. Any thoughts welcome!

Thanks

Please Login or Register  to view this content.

Great, I'll do that then! Thanks for your help!