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

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
(E-Mail Removed)



"Graham" <(E-Mail Removed)> wrote in message
news:6C9E1BC6-2DB8-49F4-A9A3-(E-Mail Removed)...
> 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

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
> (E-Mail Removed)
>
>
>
> "Graham" <(E-Mail Removed)> wrote in message
> news:6C9E1BC6-2DB8-49F4-A9A3-(E-Mail Removed)...
> > 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
>
>

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
> > (E-Mail Removed)
> >
> >
> >
> > "Graham" <(E-Mail Removed)> wrote in message
> > news:6C9E1BC6-2DB8-49F4-A9A3-(E-Mail Removed)...
> > > 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

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
> > > (E-Mail Removed)
> > >
> > >
> > >
> > > "Graham" <(E-Mail Removed)> wrote in message
> > > news:6C9E1BC6-2DB8-49F4-A9A3-(E-Mail Removed)...
> > > > 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
>

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
> > > > (E-Mail Removed)
> > > >
> > > >
> > > >
> > > > "Graham" <(E-Mail Removed)> wrote in message
> > > > news:6C9E1BC6-2DB8-49F4-A9A3-(E-Mail Removed)...
> > > > > 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