Adding a date range to filter results 2

[ecojohnson]

Hi everyone.

I am trying to create an excel report where I specify a date range on one of the worksheets. Cell A1 might be something like: 4/1/08, and cell A2 might be something like: 4/30/08.

The remaining worksheets are set up so they each have various information on them, and this information is pulled from different database queries (in other words, each worksheet has a separate query).

What I am trying to do is use the dates from the first cell as a date range to filter the data on the remaining worksheets. So, if one of my worksheets pulled all orders and order dates from a database query, I only want to pull dates from the date range on the first worksheet.

The reason for doing this is the report will be run on an ongoing basis (every month or so). When running the report, I will set the date range (the reason for the first worksheet), and I don't want to set this date range for each worksheet everytime I need to change a date. Yes, I could run a date range filter at the time of the query, but then I would have to do this mutliple times (setting up a new date range for each query). I just want to do this once.

I looked at the filters option in the help file, but it doesn't seem to allow me to filter from another worksheet (or at least I am not understanding the help file correctly).

If anyone has any ideas on how to do this, or if you need more clarification, please let me know.

Thanks.

 

[SkipVought]

Hi,

Turn on your macro recorder and record setting the filter criteria just as it is required right now.

Turn off your recorder

alt+F11 to toggle between the active sheet and the VB Editor.

Observe your recorded macro

Probably, your Criteria1 looks like this...
[tt]
Criteria1:=">=m/d/yyyy"
[/tt]
and Criteria2 looks like this...
[tt]
Criteria2:="<=m/d/yyyy"
[/tt]
Just DELETE the DATE PART and make it look like this, assuming that your date limits are on Sheet1...
[tt]
Criteria1:=">=" & Sheets("Sheet1").[A1]
Criteria2:="<=" & Sheets("Sheet1").[A2]
[/tt]
Now you will have to run that macro to set up your filter. You can run it in the Workbook_Open event automatically or from the Tools > Macro... menu.

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[ecojohnson]

Thanks Skip. I was able to get close, but the macro returns an error: "Run-time error '9': subscript out of range".

The code in question is:

Selection.AutoFilter Field:=1, Criteria1:=">=" & Sheets("Sheet1").[A1], Operator:= _
xlAnd, Criteria2:="<=" & Sheets("Sheet1").[A2]

 

[Gavona]

....Or you could set up a criteria range and use advanced filter instead of autofilter. Your sheet containing the criteria could actually be configured to be the criteria range. Use named ranges.

In the following code note the use of CurrentRegion to automatically cope with different numbers of records in your data.

Range("data1").CurrentRegion.AdvancedFilter _
Action:=xlFilterInPlace

Gavin

 

[SkipVought]

Assuming that your filter is on Sheet2...

[b]
Sheets("Sheet2").[A1].[/b]AutoFilter Field:=1, Criteria1:=">=" & Sheets("Sheet1").[A1], Operator:= _
        xlAnd, Criteria2:="<=" & Sheets("Sheet1").[A2]

Selection will be unreliable.

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[ecojohnson]

Great - thanks. I wanted to try to get this figured out before the weekend. I'll give it a shot.

Thanks again.