| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter on Multiple Worksheets
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter on Multiple Worksheets
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter on Multiple Worksheets
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter on Multiple Worksheets
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter on Multiple Worksheets
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter on Multiple Worksheets
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| autofilter linked in different worksheets | Excel Discussion (Misc queries) | |||
| Autofilter in protected worksheets | Excel Worksheet Functions | |||
| Can I autofilter across multiple worksheets? | Excel Programming | |||
| Can I autofilter across multiple worksheets? | Excel Programming | |||
| Autofilter across Worksheets | Excel Programming | |||
Linear Mode
