Autofilter on date

Mark,

Try this:

Please Login or Register  to view this content.

In that case I'd have to see a sample file experiencing the issue

Hi tigeravatar

I have managed to get it working using the following

Worksheets("Master").Range("$A:$AB").AutoFilter Field:=5, Criteria1:=xlFilterLastMonth, Operator:=xlFilterDynamic

but this filters for the previous month which is fine if your in august it will work for july but is there a way of specifiying which month it filters?

mark

SOLVED

xlFilterAllDatesInPeriodJuly does the job there is one for every month

wambaugh,

Can you please provide a sample file?

Originally Posted by tigeravatar

In that case I'd have to see a sample file experiencing the issue

wambaugh,

When I change the code to the following, it successfully displays only dates that are on or after July 1, 2013:

Please Login or Register  to view this content.

I have attached a modified version of your provided sample

Thanks tigaravatar

Yes once I changed the dateserial to UK format yes it does work but it doesn't use variables like I was after

This sheet will be used each month by people who don't know VBA code so I need to be able to pick up the month number from a cell on the lookup sheet

that way each month they only have to change the month number in cell A1 and it will only show the months data that they have put in the cell

I don't want to have to change every line of code each month to run a new report

Also by using the >= operator it will pick up data from any month after July 2013 I just want data from a specific month in a specific year

I appreciate your help


Mark

EDIT

ok i got it working with variables

Dim y, m
y = Year(Worksheets("Lookup").Range("A1").Value)
m = Month(Worksheets("Lookup").Range("A1").Value)
With Sheets("Master").Range("$A:$AB")
.Parent.AutoFilterMode = False
.AutoFilter Field:=3, Criteria1:=">=" & DateSerial(y, 1, m)
End With

i tried the following after Setting cell A1 in Lookup table to "1/6/2013"

.AutoFilter Field:=3, Criteria1:=">=" & DateSerial(y, 1, m), Operator:=xlAnd, Criteria2:="<=" & DateSerial(y, 30, m)

but for some reason it shows records from 1st June to the 30th July instead of just JUNES data

I will need a way of working out how many days in the month specified

wish I could just workout how to use a variable in the xlFilterAllDatesInPeriod method

Mark,

Why didn't you say you wanted the filter criteria to be pulled from a cell? What cell will it be pulled from? What is the data that will be in the cell?

Also, from your original post:

I want to autofilter that column so that it automatically shows only records after the 1st July 2013

And from your most recent post:

Also by using the >= operator it will pick up data from any month after July 2013 I just want data from a specific month in a specific year

That is easily accomplished, but things would go a lot easier if you would explain these things ahead of time

Assuming the user is entering the following into sheet 'Lookup' cell A1 and that cell is formatted as General when they do so:
"July 2013"

Excel will convert that into a date value of Jul-13 automatically. This VBA code can be used to set the filter:

Please Login or Register  to view this content.

You're very welcome

If that takes care of your need, please mark this thread as solved.
How to mark a thread Solved
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix"
Change to "Solved"
Click Save