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.
Graham wrote:
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
--
Dave Peterson