Move rows of data back and forth from one worksheet to another automatically

Hello, I would like to move rows of data back and forth from one worksheet to another automatically based on a date value.

One worksheet will contain "Active Development Projects" and the other worksheet will contain "Completed Development Projects." Projects are listed by row. Both worksheets have the same column headings to line up all of the data.

Once a project completion date is entered into the column titled "DE Complete Date" (column R) this should trigger that entire row to be moved to the worksheet "Completed Development Projects." Each new row that moves to the "Completed" worksheet will be added below the last row that is populated (hope this makes sense).

If the project was inadvertently moved to the "Completed Development Projects" worksheet, I would like to automatically move it back to the "Active Development Projects" worksheet using a trigger as well - maybe by removing the date from the "DE Complete Date" (column R) cell. Again, the place of this row of data back to the "Active Development Projects" could be after the last populated row in the worksheet.

I tried using the VBA code below and it worked at first, but then stopped working and it only addressed moving the row of data from Active to Completed and not back. You can see this code in the attached sample spreadsheet.

Any help to make this all happen would be greatly appreciated! Thank you in advance.

You will need worksheet_change event code in both worksheets.
I moved away from named ranges and set variables, no need for that..
also stay away from cutting entirerows just cut and paste the used columns in this case 19 cols
no need to use select and selection for simple cut and paste. so

You will see both codes are very simular just the if test and destination after cut is different

for the active development projects you need

Please Login or Register  to view this content.

for the Completed tab you need

Please Login or Register  to view this content.

the .Cells(Rows.Count, 1).End(xlUp).Offset(1) in the paste destination will make sure the moved project is always pasted at the bottom of eighter list.

Thank you so very much for this - it just about works and is amazing. However, I am not sure why, but when I add a date to one of the rows, an additional row disappears. I did limited testing on this, but took time to create the attached Word document with screen images and notes to show what is happening.
Any thoughts based on this?

I am posting another reply to hopefully get some more assistance to resolve the problem with the first worksheet when it auto moves the row of data based on inputting a completion date, it takes that row (which it should) AND the very top row (whatever is in row 2) and deletes that row completely (which it should not.

It also leaves an empty space for the row that it did move, which is not that much of a problem and I can live with that if need be.

Can anyone help with this?

I am attaching another copy of the spreadsheet for ease of reviewing.

Thank you

It took me a bit to figure out what was wrong, still dont get why. But I found a way to get it to work

this is the code for the active orders sheet

Please Login or Register  to view this content.

the code for the completed sheet is in the attachment.. have a look if this works

I didn't realize you were working on this - thank you so much! This seems to work. I am going to do some more testing before I mark this as solved, but this looks great right now! Thank you again.

Looks like this works - This is amazing - Thank you so much for your great help!