Filtering multiple sheets
 

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

Filtering multiple sheets
 

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



"Patrick C. Simonds" wrote:

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

Filtering multiple sheets
 

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" wrote:

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

--

Dave Peterson

Filtering multiple sheets
 

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?


"Dave Peterson" wrote in message
...
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" wrote:

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

--

Dave Peterson

Filtering multiple sheets
 

Figured it out


"Patrick C. Simonds" wrote in message
...
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?


"Dave Peterson" wrote in message
...
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" wrote:

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

--

Dave Peterson

Filtering multiple sheets
 

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


--

Regards,
Nigel




"Patrick C. Simonds" wrote in message
...
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