Hi Everyone
This macro works ok on the open worksheet. I would like to filter all worksheets
in the workbook
Sub Filter()
For Each wks In Selection
Selection.AutoFilter Field:=2, Criteria1:="" & DateSerial(2011, 4, 1),
Operator:=xlAnd, _
Criteria2:="<" & DateSerial(2011, 4, 30)
Next wks
End Sub

On Jun 22, 8:28*am, "Cimjet" wrote:
Hi Everyone
This macro works ok on the open worksheet. I would like to filter all worksheets
in the workbook
Sub Filter()
* * * * For Each wks In Selection
* * Selection.AutoFilter Field:=2, Criteria1:="" & DateSerial(2011, 4, 1),
Operator:=xlAnd, _
* * * * Criteria2:="<" & DateSerial(2011, 4, 30)
* * * * * * * * * *Next wks
* * *End Sub

for each wks in worksheets
ws.autofilter

Hi Don
See my other post ("Custom Filter")
"Don Guillett" wrote in message
...
On Jun 22, 8:28 am, "Cimjet" wrote:
Hi Everyone
This macro works ok on the open worksheet. I would like to filter all
worksheets
in the workbook
Sub Filter()
For Each wks In Selection
Selection.AutoFilter Field:=2, Criteria1:="" & DateSerial(2011, 4, 1),
Operator:=xlAnd, _
Criteria2:="<" & DateSerial(2011, 4, 30)
Next wks
End Sub

for each wks in worksheets
ws.autofilter

Hi Don
For each wks in worksheets ---that works ok but
ws.autofilter ----that gave me an error. Run time error 448---Named argument
not found.
here is the macro..
Sub Filter()
For Each ws In Worksheets
ws.AutoFilter Field:=2, Criteria1:="" & DateSerial(2011, 3, 1),
Operator:=xlAnd, _
Criteria2:="<" & DateSerial(2011, 3, 30)
Next ws
End Sub
Regards
Cimjet
"Don Guillett" wrote in message
...
On Jun 22, 8:28 am, "Cimjet" wrote:
Hi Everyone
This macro works ok on the open worksheet. I would like to filter all
worksheets
in the workbook
Sub Filter()
For Each wks In Selection
Selection.AutoFilter Field:=2, Criteria1:="" & DateSerial(2011, 4, 1),
Operator:=xlAnd, _
Criteria2:="<" & DateSerial(2011, 4, 30)
Next wks
End Sub

for each wks in worksheets
ws.autofilter

Hi Cimjet,

Am Wed, 22 Jun 2011 12:38:57 -0400 schrieb Cimjet:

Sub Filter()
For Each ws In Worksheets
ws.AutoFilter Field:=2, Criteria1:="" & DateSerial(2011, 3, 1),
Operator:=xlAnd, _
Criteria2:="<" & DateSerial(2011, 3, 30)
Next ws
End Sub

you have to activate each worksheet or you have to write "With ws"
Try this:
For Each ws In Worksheets
With ws.UsedRange
.AutoFilter , Field:=2, _
Criteria1:="" & DateSerial(2011, 3, 1), _
Operator:=xlAnd, _
Criteria2:="<" & DateSerial(2011, 3, 30)
End With
Next ws


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Claus
It's not working properly. I get this message..plus it's not filtering all
sheets, one is left out.
Run time error 1004---AutoFilter method of range class failed.
Claus, see my other post "Custom Filter" I'm getting the same error. but it's
filtering all sheets.
Any help would be appreciated.
Cimjet


"Claus Busch" wrote in message
...
Hi Cimjet,

Am Wed, 22 Jun 2011 12:38:57 -0400 schrieb Cimjet:

Sub Filter()
For Each ws In Worksheets
ws.AutoFilter Field:=2, Criteria1:="" & DateSerial(2011, 3, 1),
Operator:=xlAnd, _
Criteria2:="<" & DateSerial(2011, 3, 30)
Next ws
End Sub

you have to activate each worksheet or you have to write "With ws"
Try this:
For Each ws In Worksheets
With ws.UsedRange
.AutoFilter , Field:=2, _
Criteria1:="" & DateSerial(2011, 3, 1), _
Operator:=xlAnd, _
Criteria2:="<" & DateSerial(2011, 3, 30)
End With
Next ws


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus
Correction it's not filtering all sheets, it's leaving one out just like yours
"Cimjet" wrote in message
...
Hi Claus
It's not working properly. I get this message..plus it's not filtering all
sheets, one is left out.
Run time error 1004---AutoFilter method of range class failed.
Claus, see my other post "Custom Filter" I'm getting the same error. but it's
filtering all sheets.
Any help would be appreciated.
Cimjet


"Claus Busch" wrote in message
...
Hi Cimjet,

Am Wed, 22 Jun 2011 12:38:57 -0400 schrieb Cimjet:

Sub Filter()
For Each ws In Worksheets
ws.AutoFilter Field:=2, Criteria1:="" & DateSerial(2011, 3, 1),
Operator:=xlAnd, _
Criteria2:="<" & DateSerial(2011, 3, 30)
Next ws
End Sub

you have to activate each worksheet or you have to write "With ws"
Try this:
For Each ws In Worksheets
With ws.UsedRange
.AutoFilter , Field:=2, _
Criteria1:="" & DateSerial(2011, 3, 1), _
Operator:=xlAnd, _
Criteria2:="<" & DateSerial(2011, 3, 30)
End With
Next ws


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Cimjet,

Am Wed, 22 Jun 2011 13:26:27 -0400 schrieb Cimjet:

Correction it's not filtering all sheets, it's leaving one out just like yours

in my workbook it's filtering all sheets. Only DateSerial is not
working. Autofilter in VBA needs the date in MM/DD/YYYY.
If I try:
..AutoFilter Field:=2, Criteria1:="03/01/2011", _
Operator:=xlAnd, Criteria2:="<03/30/2011"

or if I put dates in F1 and F2 and try:
..AutoFilter Field:=2, _
Criteria1:="" & Range("F1").Value2, _
Operator:=xlAnd, _
Criteria2:="<" & Range("F2").Value2

everything works fine.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Claus
I'm still getting the same error. I place the proper dates in F1 and G1
Sub Filter()
For Each wsh In Worksheets
With wsh
If Not .FilterMode Then .Range("B5").AutoFilter
.Range("B5").AutoFilter , Field:=2, Criteria1:="" & Range("F1").Value2, _
Operator:=xlAnd, Criteria2:="<" & Range("G1").Value2
End With
Next
End Sub

"Claus Busch" wrote in message
...
Hi Cimjet,

Am Wed, 22 Jun 2011 13:26:27 -0400 schrieb Cimjet:

Correction it's not filtering all sheets, it's leaving one out just like
yours

in my workbook it's filtering all sheets. Only DateSerial is not
working. Autofilter in VBA needs the date in MM/DD/YYYY.
If I try:
.AutoFilter Field:=2, Criteria1:="03/01/2011", _
Operator:=xlAnd, Criteria2:="<03/30/2011"

or if I put dates in F1 and F2 and try:
.AutoFilter Field:=2, _
Criteria1:="" & Range("F1").Value2, _
Operator:=xlAnd, _
Criteria2:="<" & Range("F2").Value2

everything works fine.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Cimjet,

Am Wed, 22 Jun 2011 13:43:48 -0400 schrieb Cimjet:

I'm still getting the same error. I place the proper dates in F1 and G1
Sub Filter()
For Each wsh In Worksheets
With wsh
If Not .FilterMode Then .Range("B5").AutoFilter
.Range("B5").AutoFilter , Field:=2, Criteria1:="" & Range("F1").Value2, _
Operator:=xlAnd, Criteria2:="<" & Range("G1").Value2
End With
Next
End Sub

here everything works fine. But I have to start the macro from the sheet
in which the date is placed or I have to reference it with sheetname.
Where did your code stop? Write in first line of module:
Option Explicit
Then you have to declare wsh as worksheet, but you see where your code
stops.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus
It's stoping at " .Range("B5").AutoFilter" and same error.
Run time error 1004---AutoFilter method of range class failed.

"Claus Busch" wrote in message
...
Hi Cimjet,

Am Wed, 22 Jun 2011 13:43:48 -0400 schrieb Cimjet:

I'm still getting the same error. I place the proper dates in F1 and G1
Sub Filter()
For Each wsh In Worksheets
With wsh
If Not .FilterMode Then .Range("B5").AutoFilter
.Range("B5").AutoFilter , Field:=2, Criteria1:="" & Range("F1").Value2,
_
Operator:=xlAnd, Criteria2:="<" & Range("G1").Value2
End With
Next
End Sub

here everything works fine. But I have to start the macro from the sheet
in which the date is placed or I have to reference it with sheetname.
Where did your code stop? Write in first line of module:
Option Explicit
Then you have to declare wsh as worksheet, but you see where your code
stops.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus
The problem is on my copy. Isabelle sent me a sample file and it works fine, it
looks like the problem is on my copy only.
Cimjet
"Cimjet" wrote in message
...
Claus
It's stoping at " .Range("B5").AutoFilter" and same error.
Run time error 1004---AutoFilter method of range class failed.

"Claus Busch" wrote in message
...
Hi Cimjet,

Am Wed, 22 Jun 2011 13:43:48 -0400 schrieb Cimjet:

I'm still getting the same error. I place the proper dates in F1 and G1
Sub Filter()
For Each wsh In Worksheets
With wsh
If Not .FilterMode Then .Range("B5").AutoFilter
.Range("B5").AutoFilter , Field:=2, Criteria1:="" & Range("F1").Value2,
_
Operator:=xlAnd, Criteria2:="<" & Range("G1").Value2
End With
Next
End Sub

here everything works fine. But I have to start the macro from the sheet
in which the date is placed or I have to reference it with sheetname.
Where did your code stop? Write in first line of module:
Option Explicit
Then you have to declare wsh as worksheet, but you see where your code
stops.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Cimjet,

Am Wed, 22 Jun 2011 15:22:53 -0400 schrieb Cimjet:

The problem is on my copy. Isabelle sent me a sample file and it works fine, it
looks like the problem is on my copy only.

thank you for the feedback.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

may be that this sheet is protected ?

--
isabelle

Hi Isabelle
No the sheets and the workbook are not protected
"isabelle" wrote in message ...
may be that this sheet is protected ?

--
isabelle

Hi Claus
Just for information, I thought you would like to know, I found the problem.
My workbook has around 16 worksheets and one of them was empty, that was causing
the problem plus one sheet I had to place an autofilter on the heading and
that's it, it works like a charm.
Cimjet

"Claus Busch" wrote in message
...
Hi Cimjet,

Am Wed, 22 Jun 2011 15:22:53 -0400 schrieb Cimjet:

The problem is on my copy. Isabelle sent me a sample file and it works fine,
it
looks like the problem is on my copy only.

thank you for the feedback.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2