Apr 24 2018 06:35 AM
Apr 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.
Apr 25 2018 11:55 AM - edited Apr 25 2018 11:55 AM
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.
May 02 2018 05:49 AM
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!
Oct 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"
Oct 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.
Oct 21 2019 02:58 PM
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.
Oct 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....
Oct 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
Oct 22 2019 09:30 AM
Thanks for the help! I am receiving a syntax error. Please advise if I have this correct...
Oct 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.
Nov 22 2019 01:11 PM - edited Nov 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.
Dec 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.
Jan 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.
Apr 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 ?
Dec 10 2020 02:18 AM
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!
Aug 29 2021 11:42 AM
Hello, I tried the link and whenever I add ByVal Target As Range in the function the macro disappears as one of my macro's and cannot be run. Here is my code below.
Private Sub MoveBasedOnValue(ByVal Target As Range)
'Move row to new spreadsheet
On Error Resume Next
Application.EnableEvents = False
'If Cell that is edited is in column B and the value is completed then
If Target.Column = 2 And Target.Value = "COMPLETED - ARCHIVE" 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 & ":B" & Target.Row).Copy Sheets("Completed").Range("A" & LrowCompleted + 1).xlPasteValues
'Delete Row from Project List
Range("A" & Target.Row & ":B" & Target.Row).Delete xlShiftUp
Application.EnableEvents = True
Oct 28 2021 03:07 PM - edited Oct 28 2021 03:16 PM
I experienced the same problem as AAnger1750 but found a solution. Instead of entering the macro into VBA directly as a new module and trying to run it, since this is a (ByVal Target As Range) that doesn't have a target selected, you need to apply it directly to the sheet you want the data copied from. So, I right-clicked on my Sheet 1 Tab (where data is entered by users) and selected "View Code" from the options. I then entered the macro into the VBA box. And now it works. There's probably a better way to go about this but this is how I (a complete newbie) found to do it. Posting in the event this helps someone else.
Feb 04 2022 09:17 AM
@Smitty Smith I followed the tips you provided above to @cascherfeld and now coming across some issue. There seems to be no compile error with the code, but when I hit F5 to run it it brings out a macros pane which ask me to create a name and after I do it creates a module, but data doesn't move.
I also tried changing one of my Data sets to the indicator I'll be using which is "P", but the data did not move to the completed orders sheet.
Am I missing something? Any assistance is appreciated