I am working on a spread sheet that is a list of projects. Basically once a project is completed, i want to move that entire row to a completed projects sheet, and delete the row from the sheet it's in. My Range is columns A-U. In column U, once the project is completed i want the row automatically moved. i have attached a screenshot. Thanks in advanced.
I have attached a sample workbook for your reference. You will need to paste this code in the Project List worksheet code module in the attached file for it to work properly:
1. Alt + F11 (Access VBE)
2. Double click the icon in the Project Explorer that says Project List
3. Paste the code
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
'If Cell that is edited is in column U and the value is completed then
If Target.Column = 21 And Target.Value = "Completed" Then
'Define last row on completed worksheet to know where to place the row of data
LrowCompleted = Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Row
'Copy and paste data
Range("A" & Target.Row & ":U" & Target.Row).Copy Sheets("Completed").Range("A" & LrowCompleted + 1)
'Delete Row from Project List
Range("A" & Target.Row & ":U" & Target.Row).Delete xlShiftUp
Application.EnableEvents = True
Please note that you may need to adjust the worksheet names in the VBA code based on your actual worksheet names.
Just wanted to follow up and see if you were able to get your issue sorted out? If you're still having trouble feel free to reach back out. Always happy to help! That's what the Excel Community is here for!
Thank you for this solution. I was able to use it for my needs.
However, rows are also removed and sent to the completed sheet when I copy and paste seemingly unrelated cells to that row.
So, for a row which is not marked as "complete", I paste a few cells from another sheet. As soon as I paste, the row is moved. The cells I am pasting to not include the cell I am using to mark it as "Complete"