Autofilter on Multiple Worksheets

G

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
 
N

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
 
G

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
 
D

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
....
 
G

Graham

Nigel/Dave

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

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.
 

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