VBA filter pivot table

Euro5
Euro5 used Ask the Experts™
on
I would like to choose "FO'", "PO", "SO", etc in the filter below.
I tried Value2:="PO", Value3:="SO" but get error

  Application.ScreenUpdating = False
     ActiveSheet.PivotTables("Ship").ManualUpdate = True

      ActiveSheet.PivotTables("Ship").PivotFields("Service Type").ClearAllFilters

      ActiveSheet.PivotTables("Ship").PivotFields("Service Type").PivotFilters. _
    Add Type:=xlCaptionEquals, Value1:="FO"

  ActiveSheet.PivotTables("Ship").ManualUpdate = False
  Application.ScreenUpdating = True

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
if  you upload a dummy file, i can do it for you.

otherwise, you can record a macro while you set the filter and it will generate the code for ya.

also you can see different ways manipulating pivot table with vba in here
Top Expert 2016

Commented:
Hi,

pls try

Application.ScreenUpdating = False
Set pt = ActiveSheet.PivotTables("Ship")
With pt
    .ManualUpdate = True

    .PivotFields("Service type").ClearAllFilters
        
    For Each PtItm In .PivotFields("Service type").PivotItems
        If PtItm.Name = "FO" Or PtItm.Name = "PO" Or PtItm.Name = "SO" Then
            PtItm.Visible = True
        Else
            PtItm.Visible = False
        End If
    Next
End With
Application.ScreenUpdating = True

Open in new window

Regards

Author

Commented:
Rgonzo1971  Should PtItm be a variable?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

Commented:
Yes

Dim pt As PivotTable
Dim PtItm As PivotItem
Application.ScreenUpdating = False
Set pt = ActiveSheet.PivotTables("Ship")
With pt
    .ManualUpdate = True

    .PivotFields("Service type").ClearAllFilters
        
    For Each PtItm In .PivotFields("Service type").PivotItems
        If PtItm.Name = "FO" Or PtItm.Name = "PO" Or PtItm.Name = "SO" Then
            PtItm.Visible = True
        Else
            PtItm.Visible = False
        End If
    Next
End With
Application.ScreenUpdating = True

Open in new window

Please try this

Dim ITEM As PivotItem
Application.ScreenUpdating = False
      ActiveSheet.PivotTables("Ship").PivotFields("Service Type").ClearAllFilters
For Each ITEM In ActiveSheet.PivotTables("Ship").PivotFields("Service Type").PivotItems
If Not ITEM = "FO" And Not ITEM = ("PO") And Not ITEM = ("SO") Then
        
        ITEM.Visible = False
    End If
     
    Next

  Application.ScreenUpdating = True

Open in new window

Author

Commented:
Rgonzo1971 this selects the correct Service Types, but they are not displayed in the pivot.
I can see them in the Service Type drop down as checked, but the pivot is blank.
Is that unusual?

Author

Commented:
Excel amusant that excludes the Service Types i need to include
It includes the name which are listed in the code.

If you run the code the list will be filtered by FO, PO and SO the rest will not be visible.

Author

Commented:
I uploaded a file - this may help.
What-if-scenario-v7.2.xlsm
Please see attached. It does filter by FO, PO and SO.

Click on the macro button in sheet Temp.
What-if-scenario-v7.2.xlsm

Author

Commented:
You were right! So sorry and thanks very much!!
You welcome, Glad it was helpful.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial