Filtering multiple sheets

[Patrick C. Simonds]

Can any one tell me why the code below only seems to run on the active
worksheet? also is there any way to unfilter all 4 worksheets without
making them active?


Sub FilterData()
'
'
'
Application.ScreenUpdating = False

Module4.Disable_Events

With ActiveWorkbook.Worksheets("Relief Board")
Range("A6:K3000").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("A1:K2"), Unique:=False
Range("C6").Select
End With

With ActiveWorkbook.Worksheets("Fixed Route Operators")
Range("A6:K3000").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("A1:K2"), Unique:=False
Range("C6").Select
End With

With ActiveWorkbook.Worksheets("Paratransit Operators")
Range("A6:K3000").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("A1:K2"), Unique:=False
Range("C6").Select
End With

With ActiveWorkbook.Worksheets("Dispatchers, PSC's and CSR's")
Range("A6:K3000").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("A1:K2"), Unique:=False
Range("C6").Select
End With


Module4.Enable_Events
Application.ScreenUpdating = True


End Sub

 

[Joel]

You are missing all the periods associated with the WITH Add a period in
front of the word RANGE like below. Without the period you are only
accessing the activesheet.

Sub FilterData()
'
'
'
Application.ScreenUpdating = False

Module4.Disable_Events

With ActiveWorkbook.Worksheets("Relief Board")
.Range("A6:K3000").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
.Range("A1:K2"), Unique:=False
.Range("C6").Select
End With

With ActiveWorkbook.Worksheets("Fixed Route Operators")
.Range("A6:K3000").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
.Range("A1:K2"), Unique:=False
.Range("C6").Select
End With

With ActiveWorkbook.Worksheets("Paratransit Operators")
.Range("A6:K3000").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
.Range("A1:K2"), Unique:=False
.Range("C6").Select
End With

With ActiveWorkbook.Worksheets("Dispatchers, PSC's and CSR's")
.Range("A6:K3000").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
.Range("A1:K2"), Unique:=False
.Range("C6").Select
End With


Module4.Enable_Events
Application.ScreenUpdating = True


End Sub

 

[Dave Peterson]

When the code is in a General module (not behind a worksheet), then those
unqualifed ranges will refer to the active sheet.

With ActiveWorkbook.Worksheets("Relief Board")
.Range("A6:K3000").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=.Range("A1:K2"), Unique:=False
'you can only select a range on the activesheet
'so you have to add the .select or remove the .range("C6").select line
.Select
.Range("C6").Select
End With

The dots in front of all the range's mean that they belong to the object in the
previous with statement (Worksheets("Relief board") in this portion).

Without the dots (.range("a1:k2), too!), these unqualified ranges will point
back at the activesheet.

 

[Patrick C. Simonds]

Thanks.

The other half of the question was, is there any way to unfilter all 4
worksheets (show all data) with out making each worksheet active?

 

[Patrick C. Simonds]

Figured it out

Thanks.

The other half of the question was, is there any way to unfilter all 4
worksheets (show all data) with out making each worksheet active?

 

[Nigel]

You say 'With', then fail to fully reference the range, snippet of your code
below showing the full stop in front of the range references.


With ActiveWorkbook.Worksheets("Relief Board")
.Range("A6:K3000").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:= .Range("A1:K2"), Unique:=False
.Range("C6").Select
End With