Can someone help to modify code below so a row on sheet "Master Roster" is moved to anouther sheet "Placed" but moved to row 1 pushing other data down a row ... Private Sub Worksheet_Change(ByVal Target As Range)
'stop anything re-triggering this event macro
Application.EnableEvents = FalseOn Error GoTo ErrHnd
'test if changed cell is in column J (col. 10) and it contains P (or p)
If Target.Column = 10 And UCase(Target.Text) = "P" Then
Dim rngCell As Range
Dim rngDest As Range
Dim strRowAddr As String
'save target row address
strRowAddr = Target.Address
'find next row in destination worksheet
Set rngDest = Worksheets("Placed"). _
Range("A" & CStr(Application.Rows.Count)).End(xlUp).Offset(1, 0)'cut the source row & paste to destination
Target.EntireRow.Cut Destination:=rngDest
'remove the cut/copy range marquee
Application.CutCopyMode = False
'delete the source row
Worksheets("Master Roster").Range(strRowAddr).EntireRow.Delete _
Shift:=xlUp
End If
Application.EnableEvents = True
Exit Sub'error handler
ErrHnd:
Err.Clear
Application.EnableEvents = True
End Sub
Another Senerio... Where column J contains a p ...
If column J contained a date and wanted to move the row 3 days after todays() date...