VBA - Filter before Tomorrow

[kgkev]

i have recorded a macro and editted so it should filter a table and show anying that is before tomorrows date.

however when I run it it does not give any results.

Sub overdue()
'
' overdue Macro
'

Dim criteria As Date
criteria = Date + 1
    ActiveSheet.Range("$A$1:$P$188").AutoFilter Field:=12
    ActiveWorkbook.Worksheets("Active rejects").AutoFilter.Sort.SortFields.Clear
    ActiveSheet.Range("$A$1:$P$188").AutoFilter Field:=12, Criteria1:= _
        "<" & criteria, Operator:=xlAnd
    Sheets("Active rejects").Select
End Sub

Oddly if I click the filter arrow and check the details it is all correct and clicking OK gives the results as I expected.

 

[Jerry Sullivan]

That's interesting. The code seemed to work correctly for me when my ActiveSheet is named "Active rejects".

Is there some reason why you use the reference ActiveSheet in some places and Sheets("Active rejects") in other places?

 

[kgkev]

activesheet is defaulted when recording macros

I got rid of all the useless code - Even hard coded in tomorrows date.

Sub overdue()
    Worksheets("Active rejects").AutoFilter.Sort.SortFields.Clear
    Worksheets("Active rejects").Range("$A$2:$P$188").AutoFilter Field:=12, Criteria1:="<29/07/2011"
End Sub

and it still filters to no records.

However again if I go through the filter manually the "BEFORE" and "29/07/11" is already prefilled in the correct boxes and clicking OK gives the records I need.

 

[Peter_SSs]

Dates are often a bit tricky because of the different world formats. Try making this change

Dim criteria As Long

 

[kgkev]

Thanks for the suggestion but I have removed the "criteria" and gone back to the recorded macro and it still doesn't work

Sub overdue()
    Worksheets("Active rejects").AutoFilter.Sort.SortFields.Clear
    Worksheets("Active rejects").Range("$A$2:$P$188").AutoFilter Field:=12, Criteria1:="<29/07/2011"
End Sub

I have also tried reapplying the filter after this

    ActiveSheet.AutoFilter.ApplyFilter

and that didn't work either - Any other suggestions?

 

[Peter_SSs]

Thanks for the suggestion but I have removed the "criteria" and gone back to the recorded macro and it still doesn't work

In that case, why don't you at least try the suggestion?

 

[kgkev]

Thanks Peter.

I appreciate your persistance

 

[Jerry Sullivan]

My initial thought was that this was a date formatting problem (either in the data range to filter or the criteria), but if that were the case, I don't think this could happen....

Oddly if I click the filter arrow and check the details it is all correct and clicking OK gives the results as I expected.