Hi Bill

Try this example on a copy of your workbook
See also
http://www.rondebruin.nl/copy5.htm


Sub Copy_With_AutoFilter_2()
' This sub use the function LastRow
Dim WS As Worksheet
Dim WS2 As Worksheet
Dim rng As Range
Dim Str As String

Set WS = Sheets("MSO Tracking")
Set WS2 = Sheets("Completed MSO")
Str = Date
WS.Range("B1:IV1000").AutoFilter Field:=6, Criteria1:="" & Str
'Use a Dynamic range name, http://www.contextures.com/xlNames01.html#Dynamic
'This example filter on column G in the range (change the field if needed)

With WS.AutoFilter.Range
On Error Resume Next
' This example will not copy the header row each time
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then
'Copy the cells
rng.Copy WS2.Range("A" & LastRow(WS2) + 1)
'Delete the rows
rng.EntireRow.Delete
End If
End With
WS.AutoFilterMode = False
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Bill" wrote in message ...
I have two worksheet containing rows of data. I need the VBA code that will
scan column g on the worksheet named "MSO Tracking". When the scan
encounters a date older than todays date then I need the entire row starting
from Column B move to a worksheet named "Completed MSO". The row in the
worksheet named "MSO Tracking" should then be delete.Please help.