Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find and move a value based on multiple criteria | Excel Worksheet Functions | |||
move toi next column based on outside factors | Excel Discussion (Misc queries) | |||
when i move or copy a sheet, dates change by one day?? | Excel Discussion (Misc queries) | |||
Select and Move based upon keyword | Excel Programming | |||
Move data from on sheet to another based on month | Excel Programming |