PDA

View Full Version : Solved: AutoFilter question



CareerChange
05-06-2011, 02:47 PM
Consider the following code snippet...

call ActivateWorksheet("someSheet")
call SetFilters(22, "someValue")

Sub ActivateWorksheet(sSheet)
If ActiveSheet.Name <> sSheet Then
Worksheets(sSheet).Activate
End If
End Sub

Sub SetFilters(nField, cCriteria)
Selection.AutoFilter Field:=nField, Criteria1:=cCriteria
End Sub

The above code snippets work just fine, however I want to avoid activating the particular worksheet, so I thought that I could do something like this...

Sub SetFilters(sSheet, nField, cCriteria)
Worksheets(sSheet).AutoFilter Field:=nField, Criteria1:=cCriteria
End Sub
Simply put, why isn't activating a particular worksheet followed by the Selection.AutoFilter functionally equivalent to straight-out referencing it using Worksheets("someSheet").AutoFilter.?

Is there an easy way to (easily) accomplish what I want w/o a ton of complex coding?

Thank you!

p45cal
05-07-2011, 01:33 AM
The difference I see in your code is that in one case Autofilter is qualified with a range (Selection), and in the other by a worksheet. So if you were to include a reference to a range in your 2nd version of SetFilters would that do the trick? A guess. Untested.Worksheets(sSheet).range("AB454").AutoFilter Field:=nField, Criteria1:=cCriteria

CareerChange
05-07-2011, 11:55 AM
Thanks for the explanation, however I still don't understand how "Selection" understands the location of the autofilter after ... nonetheless, here's my hack to solve the problem...

Worksheets(sSheet).Range("B9:FC9").AutoFilter Field:=iField, Criteria1:=sCriteria