VBA to Move rows in a sheet based on the project Status

[Tiffany Gaquin]

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.

[#2]

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.

[#3]

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!

[#4]

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"

[#5]

@Matt Mickle can we send back from completed to project list?

[#6]

@Matt Mickle  Is it possible to do this on google docs. I need the spread sheet to stay on the shared drive.

[#7]

@Matt Mickle 

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

[#8]

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

[#9]

@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]

@Smitty Smith 

Thanks for the help! I am receiving a syntax error. Please advise if I have this correct...

[#11]

@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.

[#12]

@Smitty Smith 

Thanks Smitty! Works perfectly!

[#13]

@Smitty Smith 

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. 

[#14]

@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.

[#15]

@Matt Mickle 

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.

[#16]

@Matt Mickle 

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 ?

[#17]

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

[#18]

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
End If
Application.EnableEvents = True
End Sub

[#19]

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.  

[#20]

@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