Move Row based on dates
 

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.

Move Row based on dates
 

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.

Move Row based on dates
 

This deletedd all the rolls on one the wrong sheet

"Bill" wrote:

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.

Move Row based on dates
 

Working for me
You can send me (private) a test workbook if you want.
I look at then


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



"Bill" wrote in message ...
This deletedd all the rolls on one the wrong sheet

"Bill" wrote:

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.