Forum Discussion
VBA to Move rows in a sheet based on the project Status
Tiffany-
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
End If
Application.EnableEvents = True
End Sub
Please note that you may need to adjust the worksheet names in the VBA code based on your actual worksheet names.
Dear All
I have used this code and it works great.
Although, i would like to incorporate a msgbox to ask the user to confirm, before the copy/paste/delete actions are being done.
Any help would be much appreciated!
thanks
David
Matt I know you posted this awhile ago and it works but if you paste into the first worksheet if moves the data to the second one even if word completed is not added and deletes the row below - is there a solution to stop this ?
Great providing this outstanding Project to the Excel community. I have mine all setup but is there a way to have the exact same setup with using Share Mode.
Matt,
I have a similar project as Tiffany's. I tried the suggested script with minor changes and was able to have it work except for deleting the target row(s). Now, after a few edits I cannot get the script to work at all? No errors. I am sure it is maybe something simple, but I am not able to find the solution. I have pasted the script below. Any help would be appreciated.
Chris
This is working today? Not sure what changed, but working perfectly.
If anyone has a suggestion to change the copy and paste function to copy and paste values, please let me know....
Thank you,
Chris
cascherfeld To paste values, move the destination to a new line and add the PasteSpecial argument:
Range("A" & Target.Row & ":N" & Target.Row).Copy
Sheets("Meridian_Completed").Range("A" & LrowCompleted + 1).PasteSpecial Paste:=xlPasteValuesHTH
Matt Mickle Is it possible to do this on google docs. I need the spread sheet to stay on the shared drive.
Matt Mickle can we send back from completed to project list?
Tiffany-
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"
