Apply macro to all worksheets


C

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
 
Ad

Advertisements

D

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
 
C

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
 
C

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
 
C

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
 
C

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
 
Ad

Advertisements

C

Cimjet

Claus
Correction it's not filtering all sheets, it's leaving one out just like yours
Cimjet said:
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
 
C

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
 
C

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
 
C

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
 
C

Cimjet

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

Advertisements

C

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
Cimjet said:
Claus
It's stoping at " .Range("B5").AutoFilter" and same error.
Run time error 1004---AutoFilter method of range class failed.
 
C

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
 
Ad

Advertisements

C

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
 
Ad

Advertisements


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