Macro to filter date based on a selection

[moxman]

Hi,

Please help me with a macro that will filter a data table based on the value of a cell with a date (mm/dd/yy)

The filter area is on column "B:B" and the reference for the filter is on another sheet on cell "C5"

Here's the code that I tried

Please Login or Register  to view this content.

I am having an error with this line:

Please Login or Register  to view this content.

What is the correct way of doing this?

Thank you for your help

[AlphaFrog]

The argument name is Criteria1

ActiveSheet.Range("B3:T99").AutoFilter Field:=1, Criteria1:=Sheets("Formulas and Tables").Range("C5")

[moxman]

Thanks for pointing out the cause of error Alphabug!

I got it to start filtering however, it will not find the value i was hoping it to do.

It will filter out everything where it should leave the ones with the correct date in it.. Please see the attached workbook.

Thank you

Sample.xlsm

[moxman]

This is weird, I have tried to filter the other columns but they were working. However the filter field is in text e.g. "Boss" . Why is the autofilter not working on dates?

your help is very much appreciated

[AlphaFrog]

Thanks for pointing out the cause of error Alphabug!

I got it to start filtering however, it will not find the value i was hoping it to do.

It will filter out everything where it should leave the ones with the correct date in it.. Please see the attached workbook.

Thank you

Your dates in column B are Date-Time values as seen in the formula bar. The Autofilter function filters on the actual values and not the displayed values. Since the column B dates also have time values, none of them match exactly the date-only value in C5.

This will filter for all times on a given Date

Please Login or Register  to view this content.

[moxman]

I got it working!

Thank you to alphadog for reply to my thread.

It seems for "dates" to work, i need to add another line to the code.
with the "array" being the subcategory of the date. as it goes deeper the higher the value is.

I also tried your code alphadog and it works! how you came up with that is totally genius

Please Login or Register  to view this content.

[AlKey]

Hi moxman, I have a question for you. Why would you need a macro if you can use already built-in feature in Excel 2007? Simply right-click on a cell and select Filter, and Filter by selected cell's value.

[moxman]

hi AlKey

To answer your question, I am working on creating report generating macro for other users to help them save time. The filter data part is just one step in the process.

In other words, some people just dont want the hassle of doing a report manually, step-by-step and just wanted to click a button.