Apply macro to all worksheets

[Cimjet]

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

 

[Don Guillett]

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

 

[Cimjet]

Hi Don
See my other post ("Custom Filter")

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

 

[Cimjet]

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

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

 

[Claus Busch]

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

 

[Cimjet]

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

 

[Cimjet]

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

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]

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

 

[Cimjet]

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]

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

 

[Cimjet]

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

 

[Cimjet]

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

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

 

[Claus Busch]

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

 

[isabelle]

may be that this sheet is protected ?

 

[Cimjet]

Hi Isabelle
No the sheets and the workbook are not protected

 

[Cimjet]

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