AutoFilter on one sheet filters same on other sheets?

[Conan Kelly]

Hello all,

Three of my sheets in my workbook are almost identical, just slightly different data, with AutoFilter turned on in all three sheets.
I have it set up so that when I select a criteria from the AutoFilter drop-down on one of the sheets, the other
2 sheets are AutoFiltered the same way. I did this by creating the following sub procedure:

Sub SyncAutoFilters(pstrW1 As String, pstrW2 As String, pstrW3 As String)
Dim w1 As Worksheet
Dim w2 As Worksheet
Dim w3 As Worksheet

Dim pintFltrField As Integer
Dim pstrFltrRng As String
Dim pstrFltrCriteria As String
Dim pintIndex As Integer
Dim pintTest As Integer

Set w1 = Worksheets(pstrW1)
Set w2 = Worksheets(pstrW2)
Set w3 = Worksheets(pstrW3)

pintTest = 0

With w1.AutoFilter
pstrFltrRng = .Range.Address
For pintIndex = 1 To .Filters.Count
If .Filters.Item(pintIndex).On Then
pintFltrField = pintIndex
pstrFltrCriteria = .Filters.Item(pintIndex).Criteria1
pintTest = pintTest + 1
End If
Next
End With

If pintTest = 0 Then
w2.Range(pstrFltrRng).AutoFilter
w3.Range(pstrFltrRng).AutoFilter
Else
w2.Range(pstrFltrRng).AutoFilter pintFltrField, pstrFltrCriteria
w3.Range(pstrFltrRng).AutoFilter pintFltrField, pstrFltrCriteria
End If
End Sub

And then placing one of the following sub calls in the three sheets Deactivate event procedure:

SyncAutoFilters "RawData", "RawDataRP", "RawDataTB"
SyncAutoFilters "RawDataTB", "RawData", "RawDataRP"
SyncAutoFilters "RawDataRP", "RawData", "RawDataTB"

The problem is that even if there is no change in the AutoFilter, the SyncAutoFilters sub is still called and there and be a slight
hesitation.

Now also, if I select no filters but leave the auto filter turned on (showing all data) in one of the sheets, it appears to shut off
the AutoFilters in the other two sheets. Moving from the first sheet to the second is no problem because at that point they are all
still on and that is where they are shut off. But if I move from the second to the third, it looks like 2 are shut off and I get an
error.

First is there any way to test to see if the AutoFilter has even been changed before calling the sub? That way I won't get the
slight hesitation EVERY TIME I CHANGE SHEETS. I can handle the hesitation if there is a change in one of the AutoFilters, but
having to deal with it every single time will be quite annoying.

Second, how can I prevent it from completely shutting off the other 2 AutoFilters if I select no filters on the first one, so I
won't get the error when moving from the 2nd to the 3rd sheet?

Thanks for any help anyone can provide,

Conan Kelly

 

[Guest]

Use the ShowAll property to show all the data rather than Autofilter with no
criteria.

as far as not running, you would have to have some indicator that no change
has taken place. I am not aware of any builtin indicator. So you would have
to store the information someplace and compare it.

 

[Conan Kelly]

Tom,

Thanks for the info. But it doesn't look like the AutoFilter object has any events associated with it. None of the Worksheet
events will work to well either. Is there any event that you (or anyone else) can think of that can be used to trigger code to
change my indicator (whether it be a public variable or a cell on one of the sheets)?

Thanks again for all of your help,

Conan

 

[Tom Ogilvy]

have a subtotal formula refer to the data range and use the calculate event.
That will probably fire much more than you need. You could have the
subtotal calculate the average or perhaps the average and the countA of the
visible rows in a column and use that to determine if the criteria has
changed.

--
Regards,
Tom Ogilvy

Tom,

Thanks for the info. But it doesn't look like the AutoFilter object has

any events associated with it. None of the Worksheet

events will work to well either. Is there any event that you (or anyone

else) can think of that can be used to trigger code to