Custom filter


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
 
Ad

Advertisements

I

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
 
I

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 ?
 
C

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
 
C

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
 
Ad

Advertisements

C

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
 
I

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
 
Ad

Advertisements

C

Cimjet

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

Merci Isabelle
Cimjet
 
C

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
 
I

isabelle

hi Cimjet ,


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

Advertisements

C

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
 
C

Cimjet

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

Advertisements

I

isabelle

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top