Move Data to Another Sheet in top Row

Microsoft Microsoft excel 2007 full vers...
December 16, 2010 at 22:35:12
Specs: Windows 7
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 = False

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

'error handler
Application.EnableEvents = True
End Sub

See More: Move Data to Another Sheet in top Row

December 16, 2010 at 22:42:09
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...

Report •
Related Solutions

Ask Question