Macro/Private Sub: Set similar filter on several sheets simultaneously

Question

I work with sheets named; Rev00, Rev01, Rev02 etc - among other sheets in my workbook.

It would be very helpful (in order to compare the sub-summaries of different revisions) to set the exact same multiple-filter - as set in active sheet - in only all sheets beginning with "Rev".

This action should most wanted be activated by double Click in Range("A1") or somewhere like that (I dont want button on this one).

If possible next double Click in Range("A1") should reset filters.

Sub Test()
    Dim ws As Worksheet, str As String        
    For Each ws In Worksheets            
            str = Left(ws.Name, 3)                    
                If str = "Rev" Then                        
                    ' set filter as in active.sheet                    
                End If
     Next ws
End Sub

... and I am stuck .... will anyone guide me on this?

score 1 · Answer 1 · edited May 23 '17 at 10:25

1

Yes it is possible. :) Here is a basic sample on how it should work.

Sub Test()
    Dim ws As Worksheet, str As String

    For Each ws In Worksheets
        str = Left(ws.Name, 3)
        If UCase(str) = "REV" Then
            With ws
                '~~> Remove any filters
                .AutoFilterMode = False

                With <YOUR RANGE>
                    .AutoFilter Field:=<RELEVANT FIELD>, _
                                Criteria1:=<YOUR CRITERIA>

                    '
                    '~~> Rest of the code
                    '
                End With

                '~~> Remove any filters
                '.AutoFilterMode = False
            End With
        End If
    Next ws
End Sub

Here you can see Autofilter in action :)

To call the above code by clicking Range A1, you can use the Worksheet_BeforeDoubleClick event.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        '
        '~~> Your code goes here
        '
        Cancel = True
    End If
End Sub

Regarding your query about making Range A1 respond as an ON/OFF switch, you can use a boolean variable s shown HERE

edited May 23 '17 at 10:25

Community

1
1

answered Dec 09 '13 at 13:58

Siddharth Rout

147,039
17
206
250

I really dint get the criteria1 – Nils Morten Kirkedam Blakstad Dec 09 '13 at 14:05
Did you see the link below that piece of code on how to define your citeria1? – Siddharth Rout Dec 09 '13 at 14:06
I set a multipel filter in active sheet Revxx, then activate the macro that sets the exact same multipel filter in the rest of Rev's – Nils Morten Kirkedam Blakstad Dec 09 '13 at 14:07
What code are you using to set the filter in revx sheet? use that same `.Autofilter` line in place of `.AutoFilter Field:=, _ Criteria1:=` – Siddharth Rout Dec 09 '13 at 14:08
relevant criterias can be elected from any range in A1:AL1 – Nils Morten Kirkedam Blakstad Dec 09 '13 at 14:08
I use plain Excel filter (not macro based) in active sheet and want the macro to "read" all used parameters and set the very same filter in all other Rev's – Nils Morten Kirkedam Blakstad Dec 09 '13 at 14:11
Ah I see :) In that case, see [THIS](http://stackoverflow.com/questions/9489126/in-excel-vba-how-do-i-save-restore-a-user-defined-filter) – Siddharth Rout Dec 09 '13 at 14:13

Macro/Private Sub: Set similar filter on several sheets simultaneously

1 Answers1

Linked