filter by today's date

Hi I need to filter a worksheet if the date dd/mm/yy in column 5 is =today()-182
Until now I've been ok filtering by strings etc but...

Private Sub CB_Think25_Click()
 Application.ScreenUpdating = False    ' turns off screen refreshing.

    Dim LRow2            As String
    Dept = "Bakery"
    'sets the variables wsM equal to Main worksheet and wsM2 equal to Main2
    Set wsM = Sheets("Main")
Set wsM2 = Sheets("Main2")
    LRow2 = 0
    'reset main
        Sheets("main").Select
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    'select the Main2 worksheet
    With wsM2
    wsM2.UsedRange.Clear
        'select all the cells
'THIS NEXT LINE
        wsM.UsedRange.AutoFilter Field:=3, Criteria1:=Dept
        wsM.Columns("b:b").Copy .Range("a1")
        wsM.Columns("d:d").Copy .Range("b1")
        wsM.Columns("f:f").Copy .Range("c1")
        wsM.Columns("q:q").Copy .Range("e1")
        wsM.Columns("r:r").Copy .Range("g1")
        wsM.Columns("g:g").Copy .Range("h1")

Hi,

Are you wanting 2 filters set?
One by the "Dept"
One by the "Date"
A little mode detail and if possible a redacted copy of your workbook.

Hi,


Not tested but try

wsM.UsedRange.AutoFilter Field:=3, Criteria1:=Dept
wsM.UsedRange.AutoFilter Field:=5, Criteria1:=Format(Date - 182, "dd.mm.yyyy")

Hi,

try

wsM.UsedRange.AutoFilter Field:=5, Criteria1:=(Date - 182)

hi,

Could you post a copy of the workbook?

hi,

Which module is the code in?

hi,

Ok, I do not think you can do what you want. What you need to do is once the filter is set in column 3 you need to loop thru the visible rang and see if the date in column is => than the date range specified.
If it => then copy the data.

bump to top

hi,

I'll see if I can come up with something. Unless someone else provide you with the solution.

Hi,

If you have a date that you are looking for what do you want to copy? In your code you have the entire column being copied.
Do you just want the "Person" data copied to another sheet?
In the workbook you sent I see 2 dates that are <= todays date -182.

Hi,

As mentioned. I do not think you can set a filter for the date you require. You can how ever code the filtered data for "Bakery" and check the visible rows/columns to see if any date is less than or equal to your criteria. I have code that will do this. It can be expanded to look in all of the visible rows/columns. Once it sees the criteria copy the data to the desired sheet for each occurrence of the Date.
If the date only shows in 1 column the that row/all columns will be copied to the destination sheet.
Is this what you want?
lmk

OK,

I code this to loop thru the filtered rang for the specified criteria.
If in the column 5 all date are ok it will go to the next column to validate the date. If it finds a date that meets the criteria it will then copy that row to the desired sheet.
For testing I have an "Exit Sub" that will keep the rest of your code from running. You can remove it if you want.
Now if you want the same situation for each category for "Dept" you can set the code so that it will filter Each department.
Hope this helps.

Hi,

Thanks for letting me know. And, too thanks for the "Rep"