Hey Guys
So am new to VBA and am having a problem with the below:
I have 5 projects on the go and each project has 2 columns (task and status). When a task for that project is complete I should be able to type Done in the status column and it will move that task to a completed list (which is the same layout but has the that is was completed in the status column)
This all seems to go swimmingly except that the date appears in all the status columns of the completed list and not just the project for which task it was.
Would appreciate any advice
Thanks in advance, OGCV
So am new to VBA and am having a problem with the below:
Code:
Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next
Application.EnableEvents = False
If Target.Column = 2 And Target.Value = "Done" Then
LRowCompleted = Sheets("Jobs").Cells(Rows.Count, "L").End(xlUp).Row
Range("A" & Target.Row).Copy Sheets("Jobs").Range("L" & LRowCompleted + 1)
Range("M" & LRowCompleted + 1) = Format(Date, "mm/dd/yy")
Range("A" & Target.Row & ":B" & Target.Row).Delete xlShiftUp
End If
If Target.Column = 4 And Target.Value = "Done" Then
LRowCompleted = Sheets("Jobs").Cells(Rows.Count, "N").End(xlUp).Row
Range("C" & Target.Row).Copy Sheets("Jobs").Range("N" & LRowCompleted + 1)
Range("O" & LRowCompleted + 1) = Format(Date, "mm/dd/yy")
Range("C" & Target.Row & ":D" & Target.Row).Delete xlShiftUp
End If
If Target.Column = 6 And Target.Value = "Done" Then
LRowCompleted = Sheets("Jobs").Cells(Rows.Count, "P").End(xlUp).Row
Range("E" & Target.Row).Copy Sheets("Jobs").Range("P" & LRowCompleted + 1)
Range("Q" & LRowCompleted + 1) = Format(Date, "mm/dd/yy")
Range("E" & Target.Row & ":F" & Target.Row).Delete xlShiftUp
End If
If Target.Column = 8 And Target.Value = "Done" Then
LRowCompleted = Sheets("Jobs").Cells(Rows.Count, "R").End(xlUp).Row
Range("G" & Target.Row).Copy Sheets("Jobs").Range("R" & LRowCompleted + 1)
Range("S" & LRowCompleted + 1) = Format(Date, "mm/dd/yy")
Range("G" & Target.Row & ":H" & Target.Row).Delete xlShiftUp
End If
If Target.Column = 10 And Target.Value = "Done" Then
LRowCompleted = Sheets("Jobs").Cells(Rows.Count, "T").End(xlUp).Row
Range("I" & Target.Row).Copy Sheets("Jobs").Range("T" & LRowCompleted + 1)
Range("U" & LRowCompleted + 1) = Format(Date, "mm/dd/yy")
Range("I" & Target.Row & ":J" & Target.Row).Delete xlShiftUp
End If
Application.EnableEvents = True
I have 5 projects on the go and each project has 2 columns (task and status). When a task for that project is complete I should be able to type Done in the status column and it will move that task to a completed list (which is the same layout but has the that is was completed in the status column)
This all seems to go swimmingly except that the date appears in all the status columns of the completed list and not just the project for which task it was.
Would appreciate any advice
Thanks in advance, OGCV