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