04-24-2018 06:35 AM
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.
04-25-2018 11:55 AM - edited 04-25-2018 11:55 AM
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.
05-02-2018 05:49 AM
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!
10-04-2018 08:39 AM
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"
04-13-2019 01:26 AM
@Matt Mickle can we send back from completed to project list?
10-04-2019 04:08 AM
@Matt Mickle Is it possible to do this on google docs. I need the spread sheet to stay on the shared drive.
10-21-2019 02:58 PM
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
10-22-2019 08:10 AM
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
10-22-2019 08:25 AM
@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:=xlPasteValues
HTH
10-22-2019 09:30 AM
Thanks for the help! I am receiving a syntax error. Please advise if I have this correct...
10-22-2019 09:39 AM
@cascherfeld The .PasteSpecial Paste:=xlPasteValues part needs to be on the same line.
The forum wrapped the text since I didn't post with code tags.
10-22-2019 09:49 AM
11-22-2019 01:11 PM - edited 11-22-2019 01:13 PM
Hello I have a similar situation as above and was able to get the code you helped them with to work for me for the most part . The issue I am running into is when I use this code it pastes on row 1 of the sheet I am directing it to so every time something moves from sheet 1 to sheet 2 it is pasting over my first row of information instead of the next blank row. I am not sure how to resolve this if you have any suggestions I would really appreciate it.
12-23-2019 11:04 AM
@Smitty Smith I was wondering if you got the issue resolved? I am having the same issue where I paste data into the spreadsheet and then it cuts row 2 and past to sheet 2.
01-09-2020 09:16 AM
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.
04-13-2020 12:02 AM
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 ?
12-10-2020 02:18 AM
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