Change autofilter based on date in other cell

[jeston]

Hello, I'm pretty sure this will be easy, but I just don't have the VBA knowledge to do it myself. Bear with me while I try to explain what we've got going on. Basically, we've got a workbook with two sheets, sheet1 and sheet2. On sheet2 we do a headcount every 30 minutes. So, in column A we've got the date we're currently doing a headcount for and in column B we have the time starting at 00:00, 00:30 all the way to 23:30. We have a filter set up on the date column where we select the date we want to enter headcounts for.

So what I'd like to do, and I'm not even sure I actually need a macro for this, is change the date filter to be the date we enter into another cell (A1) on sheet1. So, if we go to sheet1 and enter the date 4/2/2011, the filter would filter out the correct dates and leave us with only the entries for 4/2/2011.

I just woke up, so pardon me if I didn't make something clear enough, and thanks for the help!


Edit: Well, I've got some code working for the most part, but now need to know where to put it so that it runs every time I change the date field. For this test I put the date that would have been entered in the cell on sheet1 in cell D3. However, I need to run the macro every time I change the date to get it to update the filter.

Sub FilterByDate()
Dim dDate As Date
dDate = Range("D3")
Range("A1").AutoFilter Field:=1, Criteria1:=dDate
End Sub


Edit2: More modifications. Got the date input on Sheet1 cell A1 and have the macro run every time Sheet1 is changed (the date is the only thing that gets changed)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dDate As Date
dDate = Sheets("Sheet1").Range("A1")
Sheets("Sheet2").Range("A1").AutoFilter Field:=1, Criteria1:=dDate
End Sub


I guess this works as well as it needs to, thanks anyway.

[jeffreybrown]

Try this...

Place this code in the worksheet module for the sheet which houses the cell you will change the date in.

Please Login or Register  to view this content.

[jeston]

Try this...

Place this code in the worksheet module for the sheet which houses the cell you will change the date in.

Please Login or Register  to view this content.

Even better, thank you!