Filtering to find data created yesterday

[polykid]

Morning all,

Firstly, the version I'm using is Excel 2007.

I wrote a macro to filter my spreadsheets then copy and paste the relevant data into a new spreadsheet. The criteria used are: find all data created on the previous business day & find data that was either "new" or "updated" on that day.

Initially it worked fine but when I put it in my "live" spreadsheet, the filter now doesn't work as intended.

I have tried changing the date format & using data validation in the column containing the 2nd criteria. As the code didn't change prior to me placing it in the "live" sheet, I've concluded (rightly or wrongly) that the problem is with the spreadsheet rather than the code.

Anyway, the code is shown below. Thanks in advance for any help provided!

Sub dailystats()
'
' dailystats Macro
'

    Dim xDay As String
    Dim xDate As Date
    
    Application.ScreenUpdating = False
    Sheets("PAC").Activate
    Rows("1:1").Select
    selection.AutoFilter
 
    xDay = Format(Date, "dddd")
 
    If xDay = "Monday" Then
    xDate = Date - 3
    Else
    xDate = Date - 1
    End If
 
    ActiveSheet.Range("$A$1:$Z$399").AutoFilter Field:=1, Criteria1:=xDate
    ActiveSheet.Range("$A$1:$Z$399").AutoFilter Field:=3, Criteria1:="NEW", _
        Operator:=xlOr, Criteria2:="UPDATE"