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" wrote:

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 wrote:

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

"Nigel" wrote:

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

--

Regards,
Nigel




"Graham" wrote in message
...
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



--

Dave Peterson