Custom filter

  • Thread starter Thread starter Cimjet
  • Start date Start date
C

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
 
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
 
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 ?
 
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
 
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
 
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
 
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
 
I'm using XL03 and will need to play with it. the problem is on my copy only.

Merci Isabelle
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
 
hi Cimjet ,


.Range("A1").AutoFilter , Field:=2, Criteria1:=">=" & DateSerial(2011, 4, 1), _
Operator:=xlAnd, Criteria2:="<=" & DateSerial(2011, 4, 30)
 
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
J'ai apprécié ton aide et pensait que tu serais intéressé de connaitre le
problème.
merci encore.
 
yes, i really appreciate this return, to know what can be the cause error messages is beneficial for everyone,
thanks again.
 
Back
Top