Months auto-filter with year twist
 

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 as
well.

TIA


--
yadaaa
------------------------------------------------------------------------
yadaaa's Profile: http://www.excelforum.com/member.php...o&userid=35130
View this thread: http://www.excelforum.com/showthread...hreadid=550611

Months auto-filter with year twist
 

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.
--
Gary''s Student


"yadaaa" wrote:


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 as
well.

TIA


--
yadaaa
------------------------------------------------------------------------
yadaaa's Profile: http://www.excelforum.com/member.php...o&userid=35130
View this thread: http://www.excelforum.com/showthread...hreadid=550611

Months auto-filter with year twist
 

but if i add a column for year, i won't be able to add a diff year for
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 from
the toggle button?


--
yadaaa
------------------------------------------------------------------------
yadaaa's Profile: http://www.excelforum.com/member.php...o&userid=35130
View this thread: http://www.excelforum.com/showthread...hreadid=550611

Months auto-filter with year twist
 

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

--
Gary's Student


"yadaaa" wrote:


but if i add a column for year, i won't be able to add a diff year for
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 from
the toggle button?


--
yadaaa
------------------------------------------------------------------------
yadaaa's Profile: http://www.excelforum.com/member.php...o&userid=35130
View this thread: http://www.excelforum.com/showthread...hreadid=550611

Months auto-filter with year twist
 

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

--
Don Guillett
SalesAid Software

"yadaaa" wrote in
message ...

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 as
well.

TIA


--
yadaaa
------------------------------------------------------------------------
yadaaa's Profile:
http://www.excelforum.com/member.php...o&userid=35130
View this thread: http://www.excelforum.com/showthread...hreadid=550611