Macro to Auto Filter by Input Box DATES ?

**Originalgoth** · 06-10-2010, 02:01 PM

Hi All,
Thanks for all the help & tips I've got from this forum so far.

I'm trying to develop this code to ask the user to input the Start Date & End Date of a report (I've got 2 Input Boxes).

With the inputed responses, I would like to run an Auto Filter with these dates as the criterion.

Here's what I've got so far, but it doesn't work.....It's not filtering anything apart from the first record, and that's probably only because it's there !!!!!
I've used dates that I know there are records for but they don't get filtered.

To build this macro I started with the Input Boxes, got them going & then started a seperate macro to do the Auto Filter (with selecting dates from the lists in the criteria box), then let it do that filter, then copied the main part of that macro onto the end of the first macro & changed the original dates to the Input Box names.
Maybe that's where I'm going wrong ?????

Column F is formatted as Date, dd-mm-yyyy

Please Login or Register  to view this content.

I haven't got any validation checks in the macro as you can see, because I'm not sure of the syntax of Date ones so if anyone can help me with that as well, that would be much appreciated. FWI, the dates can be back to Jan-2008 and as new as last week.

On that part I would like to paste the dates from the Input Boxes to the 2 cells at the end of my macro.
I have looked on here to see how to paste Input Box responses but couldn't find anything as simple.

As the next part of this process, I'll be copying the filtered records to Sheet 3 so again, any help with that would be appreciated.
I should be able to at least start the macro, but where I would need help is telling the macro to stop when it comes to the last filtered record.

Thanks again for all the help.

John

**[email protected]** · 06-10-2010, 02:39 PM

You will need to do some editing, but this should do the trick.

Please Login or Register  to view this content.

**Originalgoth** · 06-11-2010, 05:56 PM

Thanks for the code Hutch,

I've tried editing it to suit my needs by comparing it to my original code for the Filter but don't seem to be making a good job of it.

Should I change your
Range("A1:T65536").Sort Key1:=Range("Q1"),
to Range(F5:F560).Sort Key1:=Range("F5"),

The reason I ask this is because when I went with all columns (A5:AD560) it put the Auto Filter dropdowns on all columns.

I've commented out your code to ADD Sheet, and changed the name to Sheet3 (that's where the report page is pulling the info from) so this is what my code looks like now....

Please Login or Register  to view this content.

When I run this, it still doesn't show any results.

I've attached the file here so you can see what's happening / Not happening

The Macro in there called "Filter" is my original idea on it.

Thanks again for the help so far.

John

**Deamo** · 06-11-2010, 06:51 PM

I've attached a copy of your spredsheet.

The code as it stands now - still uses John's Module & ctrl + F to run..

Please Login or Register  to view this content.

as Excel likes to guess what format the date should be in, when using dates in autofilters in VBA & excel, its best to use the Clng(DateValue(date)) - as this converts the date to a Long data type, which keeps it in the format you want.

**Originalgoth** · 06-12-2010, 01:00 AM

Thanks for the help Hutch & Deamo,

With a bit of pigery Jokery with Deamos code I've got it to work but I did have to change a couple of things before it would work.

Please Login or Register  to view this content.

In the Copy Line I did change the "End(xlUp)" to End(xlDown) and that seems to have solved the issue I was having with it copying the headings from Sheet 1.
It seems to be working as I wanted it, is it OK to have an DOWN & an UP?

I'll mark this one as SOLVED

Thanks again for all of the help from Both of you so far.

Best Wishes
John