Months auto-filter with year twist

  • Thread starter Thread starter yadaaa
  • Start date Start date
Y

yadaaa

Hello,

I made a new toolbar with 12 buttons, each running a macro:

Selection.autofilter Field:=12, Criteria1:="=??/04/????"
Operator:=xlAnd

for april,

Selection.autofilter Field:=12, Criteria1:="=??/05/????"
Operator:=xlAnd

for may, etc.

but this gives me this month of all years.

i need a toggle button, that once pressed will put "2005", for example
in this macros inswtead of the ????. and i will make one for 2006 a
well.

TI
 
The easiest way to use autofilter with multiple criteria (month and year) is
to introduce an additional column in the worksheet.

For example you have the date in column 12 (column L). If you inserted a
new column with the formula:
=YEAR(L2) and copy down (say in column Z)
then you could use:
Selection.autofilter Field:=26, Criteria1:="1997"
or something similar.
 
but if i add a column for year, i won't be able to add a diff year fo
the same row, it would be only the year entered in that cell.

can't i just put some vb code instead of the "????" that will feed fro
the toggle button
 
I entered the following in column L

dates
04/11/2005
11/12/2003
16/12/2003
15/02/2006
25/09/2003
14/03/2004
22/05/2006
22/05/2001
28/10/2005
19/12/2005
11/04/2005
21/07/2004
21/11/2003
21/02/2004
07/10/2004
28/06/2004
18/09/2005
24/09/2005
18/03/2006
27/05/2006
10/09/2004
09/02/2004
26/01/2004
26/08/2005
28/03/2004
17/06/2005
06/04/2005
26/04/2004
25/01/2006
20/01/2004
20/10/2004
12/01/2004

as dates in text format in format "dd/mm/yyy"

After applying:

Sub yadaaa()
'
Selection.AutoFilter
Selection.AutoFilter Field:=12, Criteria1:="=*/05/*", Operator:=xlAnd, _
Criteria2:="=*2006*"
End Sub

got:

dates
22/05/2006
27/05/2006
 
One way might be to store the variable in a cell and use that reference

Sub togglenum()
If Range("f1") = 2006 Then
Range("f1") = 2005
Else
If Range("f1") = 2005 Then
Range("f1") = 2006
End If
End If
End Sub
 
Back
Top