I'ld like to automate a custom filter I use frequently
every day.

It first filters on a couple of criteria which are no
problem, but then I need to filter on whatever today's
date is (less than or equal to).

I've tried a couple of ways, including getting it to copy
from a cell containing =today() and pasting the value in,
but that doesn't work - it just leaves it blank, which is
worse than useless.

I suspect a bit of VBA will solve this, but I'm not too
hot on that. Anyone any bright ideas? TIA

Hi Mike
Filtering by dates is tricky but the macro below allows
the user to enter two dates to select a period in a
msgbox.
The dates are (converted to a Long?). I'm new at macro's
myself but I can assure you it works. Hopefully you can
use and adapt the code to suit your requirements.
I hasten to add this macro is not my own but has come
from post's viewed in this group and examples from
Patrick Molloy.

HTH
Bob C.

Sub Current_Month_Amount_Transfer()
'
Dim dDate As Date
Dim fDate As Date

Range("A4").Select
ActiveSheet.AutoFilterMode =
False ' Take the Autofilter off
Application.ScreenUpdating = False
dDate = CDate(InputBox("ENTER A DATE BEGINNING WITH
THE 5th DAY OF MONTH"))
Range("K4").Value = dDate
fDate = CDate(InputBox("ENTER DATE BEGINNING WITH
5th DAY OF FOLLOWING MONTH"))
Range("K5").Value = fDate
MyFirstDate = Range("K4") * 1 '
force to a LONG
MySecondDate = Range("K5") * 1 '
force to a LONG
Range("A3").AutoFilter Field:=1, Criteria1:="=" &
MyFirstDate, Operator:=xlAnd _
, Criteria2:="<" & MySecondDate
Range("G1").Copy
Sheets("Monthly").Select
Columns("B:B").Select
Selection.Find(What:="", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(-1, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("WESTPAC TRANSACTION DATA").Select
Application.CutCopyMode = False
ActiveSheet.AutoFilterMode =
False ' Take the Autofilter off
Range("K4:K9").ClearContents
Application.Run "'10WESTPAC TRANSACTIONS.xls'!
Westpac_Transaction_Special_Filter"
Application.ScreenUpdating = True
Sheets("Monthly").Select
Application.Run "'10WESTPAC TRANSACTIONS.xls'!
Move_To_Graph"

End Sub

-----Original Message-----
I'ld like to automate a custom filter I use frequently
every day.

It first filters on a couple of criteria which are no
problem, but then I need to filter on whatever today's
date is (less than or equal to).

I've tried a couple of ways, including getting it to
copy
from a cell containing =today() and pasting the value
in,
but that doesn't work - it just leaves it blank, which
is
worse than useless.

I suspect a bit of VBA will solve this, but I'm not too
hot on that. Anyone any bright ideas? TIA

.

Hi Mike

Here's a manual way or using a small macro:

Manual:
Put =TODAY() in a cell,say G1
Call autofilter.
Copy G1.
Select the drop down arrow of the date column and choose CUSTOM.
In the left box choose "less than or equal to"
With cursor in right box press CTRL-V.
Then OK should give you your dates.

Macro:
Sub aDate()
Crit = "<=" & Date
Set rng = Sheets("Sheet1").Range("A1").CurrentRegion
rng.AutoFilter Field:=4, Criteria1:=Crit 'assuming dates are in
column D
' stop here if you want to look at the filter or code to copy to another
location
Selection.AutoFilter
End Sub

CHORDially,
Art Farrell


"Mike Boardman" wrote in message
...
I'ld like to automate a custom filter I use frequently
every day.

It first filters on a couple of criteria which are no
problem, but then I need to filter on whatever today's
date is (less than or equal to).

I've tried a couple of ways, including getting it to copy
from a cell containing =today() and pasting the value in,
but that doesn't work - it just leaves it blank, which is
worse than useless.

I suspect a bit of VBA will solve this, but I'm not too
hot on that. Anyone any bright ideas? TIA