Macro for applying filter to all sheets.

[strongman86]

Hi all,

I'm looking for a macro that applies the Label filter -> does not contain APPLE to Cell A2 in all sheets in workbook every time the workbook is opened. Thank you.

 

[Yongle]

Try this

Place code in ThisWorbook Module
- does not work if placed in a standard module

Private Sub Workbook_Open()
    AppleCore
End Sub

Private Sub AppleCore()
    Const X = "<>*APPLE*"
    Dim W As Worksheet, R As Range
    On Error Resume Next
    
    For Each W In ThisWorkbook.Worksheets
        Set R = W.Cells(2, 1).Resize(W.Rows.Count - 1, W.Columns.Count)
        W.ShowAllData
        R.AutoFilter Field:=1, Criteria1:=X, Operator:=xlAnd
    Next
End Sub

 

[strongman86]

Hi Yongle,

This doesn't seem to work. Forgot to mention all sheets are pivottables if this makes any difference.

 

[Yongle]

Forgot to mention all sheets are pivottables

Yes - that would make a difference

This is waht I was using to test
Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​
1​
2​	Header1	Header2	Header3
3​	The apple is red	07/08/2019​	104​
4​	TheOrange is orange	02/08/2019​	420​
5​	The pear is green	06/08/2019​	252​
6​	The apple is red	11/08/2019​	883​
7​	TheOrange is orange	09/08/2019​	558​
8​	The pear is green	08/08/2019​	101​
9​	The apple is red	04/08/2019​	153​
10​	TheOrange is orange	11/08/2019​	484​
11​	The pear is green	03/08/2019​	401​
12​	The apple is red	09/08/2019​	568​
13​
14​

Sheet: Sheet1

 

[Yongle]

You could try this

Sub Possibly()
    Dim ws As Worksheet
    On Error Resume Next
    For Each ws In ThisWorkbook.Worksheets
        ws.PivotTables(1).PivotFields(1).ClearAllFilters
        ws.PivotTables(1).PivotFields(1).PivotFilters.Add2 Type:=xlCaptionContains, Value1:="APPLE"
    Next
End Sub

If above does not work
- try recording a macro when manually amending the filter
- modify the recorded macro to give you what you require

 

[strongman86]

Yongle,

Works other way around. I need it to not contain APPLE

 

[strongman86]

Nevermind. I fixed it. Thanks for your help.