Custom filter

[Cimjet]

Hi Everyone
This macro does a custom filter on column B which are dates.
I would like to have it done on all Tabs in the workbook and is it possible to
have the two criteria "Start date and end date"
variable with a popup menu
This only works on one sheet.
Sub Filter()
For Each Worksheet In Workbooks
Selection.AutoFilter Field:=2, Criteria1:=">4/1/2011", Operator:=xlAnd, _
Criteria2:="<4/30/2011"
Next
End Sub
Regards
Cimjet

 

[isabelle]

hi Cimjet,


For Each wsh In Worksheets
With wsh
.Range("A1").AutoFilter
If Not .FilterMode Then .Range("A1").AutoFilter
.Range("A1").AutoFilter , Field:=2, Criteria1:=">" & DateSerial(2011, 4, 1), _
Operator:=xlAnd, Criteria2:="<" & DateSerial(2011, 4, 30)
End With
Next

 

[Cimjet]

Bonjour Isabelle
Merci pour ton aide, mais ca marche pas.
Cimjet

 

[isabelle]

bonjour Cimjet,

il faudrait dire ce qui ce passe,
est ce qu'il y a un message d'erreur ?
dans l'exemple donné, j'ai mis le filtre débutant en cellule A1 est ce bien le cas ?

 

[Cimjet]

Hi Isabelle
Will stay in english for others to read.
Your macro works but I get everytime this message.
Run time error 1004---AutoFilter method of range of class failed.
Can you solve that
Regards
Cimjet

 

[Cimjet]

Isabelle
This is the correct version, but with that Run time error 1004
Sub Filter()
For Each wsh In Worksheets
With wsh
.Range("B5").AutoFilter
If Not .FilterMode Then .Range("B5").AutoFilter
.Range("B5").AutoFilter , Field:=2, Criteria1:=">" & DateSerial(2011, 4, 1),
_
Operator:=xlAnd, Criteria2:="<" & DateSerial(2011, 4, 30)
End With
Next
End Sub

 

[isabelle]

hi Cimjet,

i try to reproduce the error, but without success, can be a problem on your file.
can you open the attached file and tell me if the error occurs on it.


http://cjoint.com/?AFwuv0LUghK

 

[isabelle]

is it been run with a CommandButton ?

 

[Cimjet]

Isabelle
Not the Active X button, I'm using the Form button.
Does it matter?

 

[Cimjet]

Hi Isabelle
Strange, your copy works fine, I will need to look at the copy I'm working with.
Many thanks for your help Isabelle
Un gros merci
Cimjet

 

[isabelle]

hi,

yes it can make a difference if you used a CommandButton on ver xl2002 - xl2003, i dont know for others ver.
the property "TakeFocusOnClick" must be set to False

 

[Cimjet]

I'm using XL03 and will need to play with it. the problem is on my copy only.

Merci Isabelle
Cimjet

 

[isabelle]

if you can put your file on cjoint, i can check it.

 

[Cimjet]

Isabelle
I took an older file and the problem is gone.
I need a small modification to this line >Criteria1:=">" & DateSerial(2011, 4,
1),
That line exclude the first of the month and I would like to put ">=" to include
the first but don't know the syntax.
Cimjet

 

[isabelle]

hi Cimjet ,


.Range("A1").AutoFilter , Field:=2, Criteria1:=">=" & DateSerial(2011, 4, 1), _
Operator:=xlAnd, Criteria2:="<=" & DateSerial(2011, 4, 30)

 

[Cimjet]

Merci beaucoup pour votre aide, Isabelle
Cimjet

 

[Cimjet]

Hi Isabelle
Just for information, I found the problem.
My workbook has around 16 worksheets and one of them was empty, that was causing
tthe problem plus one sheet I had to place a autofilter on the heading and
Voila, it works like a charm.
Cimjet

 

[isabelle]

hi Cimjet,

thanks for the feedback,
bonne continuité,

 

[Cimjet]

Isabelle
J'ai apprécié ton aide et pensait que tu serais intéressé de connaitre le
problème.
merci encore.

 

[isabelle]

yes, i really appreciate this return, to know what can be the cause error messages is beneficial for everyone,
thanks again.