asked on

VBA filter pivot table

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

Professor J

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

Rgonzo1971

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

Euro5

ASKER

Rgonzo1971 Should PtItm be a variable?

Rgonzo1971

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

Excel amusant

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

Euro5

ASKER

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?

Euro5

ASKER

Excel amusant that excludes the Service Types i need to include

Excel amusant

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.

Euro5

ASKER

I uploaded a file - this may help.
What-if-scenario-v7.2.xlsm

ASKER CERTIFIED SOLUTION

Excel amusant

membership

This solution is only available to members.