Autofilter on Multiple Worksheets

[Graham]

Hi

I have the following code which will autofilter data based on a cell value
on another sheet.

What I would like to achieve is to filter data on all sheets with a name
beginning with DATA e.g. DATA, DATAJAN, DATAFEB etc.

Can this be achieved with a wildcard somehow?


Dim WB As Workbook
Dim SH1 As Worksheet
Dim SH2 As Worksheet
Dim rng As Range
Dim sStr As String

Set WB = ActiveWorkbook
Set SH1 = WB.Sheets("Insurer")
Set SH2 = WB.Sheets("DATA")

sStr = SH1.Range("E2").Value


Worksheets("DATA").Select
ActiveSheet.AutoFilterMode = False
Range("A1").AutoFilter
Range("A1").AutoFilter Field:=1, Criteria1:=sStr



thanks in advance

Graham

 

[Nigel]

Use the worksheets collection and test the sheet name

e.g.

Dim wS as Worksheet
For Each wS in Worksheets
If Left(wS.Name, 4) = "DATA" then
' your code to apply the filter
End If
Next

 

[Graham]

Thanks for your help Nigel

I understand your method for selecting relevant sheets but I'm having
problems applying it to the existing code

The new code below is filtering data only on the active sheet, I'm guessing
it has something to do with < ActiveSheet.AutoFilterMode = False>

Any ideas?

Dim WB As Workbook
Dim SH1 As Worksheet
Dim SH2 As Worksheet
Dim rng As Range
Dim sStr As String
Dim wS As Worksheet

Set WB = ActiveWorkbook
Set SH1 = WB.Sheets("Insurer")
Set SH2 = WB.Sheets("DATA")

sStr = SH1.Range("E2").Value


For Each wS In Worksheets
If Left(wS.Name, 4) = "DATA" Then

ActiveSheet.AutoFilterMode = False
Range("A1").AutoFilter
Range("A1").AutoFilter Field:=1, Criteria1:=sStr

End If



thanks
Graham

 

[Dave Peterson]

For Each wS In Worksheets
If Left(wS.Name, 4) = "DATA" Then
ws.AutoFilterMode = False
ws.Range("A1").AutoFilter
ws.Range("A1").AutoFilter Field:=1, Criteria1:=sStr
End If
....

 

[Graham]

Nigel/Dave

Thanks very much for your help
Now all that's left for me is to adapt all the formulas to subtotals

 

[Dave Peterson]

If you're inserting the =subtotal()'s using data|subtotals, then you may not
want to use autofilter with this.

Try it manually first and you'll see what I mean.