Forum Discussion
VBA to Move rows in a sheet based on the project Status
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.
19 Replies
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 SubI 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.
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 SubPlease 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.
