Identify and move data to another worksheet in Excel

add a conditional formatting as

select area
click conditional formatting
select new rule
select use a formula, enter

=$G5 < $H5

then, select a color and click ok...

follw the steps here
https://www.extendoffice.com/documents/excel/3799-excel-highlight-row-based-on-multiple-cell-values.html
29050243.xlsx

Thank you....does not seem to work when the data is connected via an access database.

Sounds good Alan but to many steps...prefer VBA to do this automatically....thanks for responding.

Actually want to copy the data to another worksheet without deleting it from the main.

HainKurt....got the conditional formatting to work...now to copy the data to another worksheet.

I applied formatting to only 20 rows I guess...
make sure it is applied to all rows :)
access or manual entry, it should work as is

Got it to work ....thanks!  Now I need to copy those rows to another sheet called PASS_DUE_REPORT

Hi Luis,

Not sure exactly where you are at, but if you just need to copy to the other sheet, then do as I suggested above:

Filter as appropriate
Select the range required
Alt + :
Copy
Paste

Post back if it still isn't working.

Thanks,

Alan.

Thanks but I would prefer a VBA code to do this....

Hi Luis,

Just confirming - you want VBA or VB Script?  Your question is flagged VB Script.

Thanks,

Alan.

VBA

sorry, is conditional formatting not working for you?

why do you need vba code? or, do you want a huge inefficient function that will run on a click of button?
I dont get it...

I would like to only copy those colored rows to another worksheet...the thing is that I have thousands of lines of data....

Conditional Formatting works to identify those....

L5 : =$G5 < $H5
copy down, you will get TRUE/FALSE

extend conditional formatting to include col L
add a filter to column names, row 4

now, you have conditional formatting + filter
29050243.xlsx

Thanks But I need this information on the other sheet that I described....

Hi Luis,

May I suggest you revisit the solution I outlined above.

It would take a minute or so from start to finish, and you'd have the information in the other sheet.

Alan.

You can get a Filter and Copy process using Advanced Filter.

The Advanced Filter expects 3 entries:

Source of Data
Source of Criteria
Destination of Filtered data

If you want to copy to another sheet, ensure the destination sheet is selected before starting the wizard and the cursor is in a blank part of the sheet otherwise the wizard will try to select ranges automatically.

The running of the Advanced Filter can be automated with VBA; particularly useful if you need to clear the destination before re-running the Filter.

Thanks
Rob H

Hi Luis,

Try below, which will create a copy of "DOCK_DATE" sheet and will delete unwanted rows and highlight wanted rows:

Sub MoveData()
Dim SrcWs As Worksheet, NewSh As Worksheet, xWs As Worksheet
Dim NewLR As Long, i As Long

'Disable Events
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
End With

'Delete Report Sheet if it exists
Application.DisplayAlerts = False
For Each xWs In Application.ActiveWorkbook.Worksheets
    If xWs.Name = "PASS_DUE_REPORT" Then
        xWs.Delete
    End If
Next
Application.DisplayAlerts = True

'Set Variables
Set SrcWs = Worksheets("DOCK_DATE")

'Make a Copy of DataSheet
Application.CopyObjectsWithCells = False
SrcWs.Copy After:=SrcWs
Application.CopyObjectsWithCells = True
ActiveSheet.Name = "PASS_DUE_REPORT"
Set NewSh = Worksheets("PASS_DUE_REPORT")
NewSh.Activate

NewLR = NewSh.Range("A" & Rows.Count).End(xlUp).Row + 1

For i = NewLR To 5 Step -1
    If NewSh.Cells(i, 8).Value < NewSh.Cells(i, 7).Value Or NewSh.Cells(i, 8).Value = NewSh.Cells(i, 7).Value Then
        NewSh.Rows(i).EntireRow.Delete
    End If
    If NewSh.Cells(i, 8).Value > NewSh.Cells(i, 7).Value Then
        NewSh.Rows(i).Interior.Color = 255
        NewSh.Rows(i).Font.ColorIndex = xlAutomatic
    End If
Next i

NewSh.Columns.EntireColumn.AutoFit

'Enable Events
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Select allOpen in new window

Please find attached for your reference....
Kindly note you have PO # 500096 is not valid rows to be in your "PASS_DUE_REPORT" sheet. Required date is 12-09-17 and On-Dock-Date is 27-09-16, may be its typo error in years. But its not valid for above code.

Hope this helps.
Luis--Users-lfreund-Desktop-DATES_2.xlsm

Thank you very much!