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