I hava some future Data, and I need to filter out this data as soon as it becmoes outdated based on yesterday’s date. I used macro record to see ho to filter out by date, and I used the code to make a small script.
The problem is that when I run the code from macro myself, all the data is filtering out, instead of the outdated.
Dim WS_Count As Integer Dim I As Integer ' Set WS_Count equal to the number of worksheets in the active ' workbook. WS_Count = ActiveWorkbook.Worksheets.Count ' Begin the loop. For I = 1 To WS_Count ' Insert your code here. ' The following line shows how to reference a sheet within ' the loop by displaying the worksheet name in a dialog box. Set ws = ActiveWorkbook.Worksheets(I) A1 = ws.Range("A1").Value If (InStr(A1, "HEDGE POSITION") <> 0) And ws.AutoFilterMode = True Then Dat = CStr(Date - 1) datt = ">" & Dat MsgBox datt ws.Range("$A$4:$P$40").AutoFilter Field:=12, Criteria1:= _ datt, Operator:=xlAnd End If Next I
So this is the code recorded by macro recorder. It doesn’t work when I run it myself (all data vanishes)
ActiveSheet.Range("$A$4:$P$40").AutoFilter Field:=12, Criteria1:= _ ">16/12/2019", Operator:=xlAnd