Selecting multiple items in excel pivot table filter

Question

I am using vba to create a pivot table. The table has some filters and i am unable to select multiple items from the filter.

I recorded a macro and used the same functions as the system generated code but i am getting

"Run-time error '1004': Unable to set the CurrentPage Property of the PivotField Class"

Below is the code that is used to create the pivot table, which works:

Dim objTable As PivotTable
    Dim objField As PivotField

    ' Select the sheet and first cell of the table that contains the data.
    ActiveWorkbook.Sheets("All Projects - iNexus").Select
    Range("A1").Select

    ' Create the PivotTable object based on the Employee data on Sheet1.
    Set objTable = ActiveSheet.PivotTableWizard

    ' Specify row and column fields.
    Set objField = objTable.PivotFields("Product")
    objField.Orientation = xlRowField
    Set objField = objTable.PivotFields("SPA Value Category")
    objField.Orientation = xlColumnField

    ' Specify a data field with its summary
    ' function and format.
    Set objField = objTable.PivotFields("2014 FY Value ($)")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "$ #,##0"

    Set objField = objTable.PivotFields("2015 FY Value ($)")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "$ #,##0"

    ActiveSheet.Name = "iNexus Pivot"

    ' Rename the pivot table
    With Sheets("iNexus Pivot")
        .PivotTables(1).Name = "PivotTable2"
    End With

    With Sheets("iNexus Pivot").PivotTables("PivotTable2").DataPivotField
        .Orientation = xlColumnField
    End With

THis is code that i got from the recorded macro to select multiple items from the recorded macro:

ActiveSheet.PivotTables("PivotTable2").PivotFields("Project Status"). _
    CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Project Status")
        .PivotItems("A").Visible = False
        .PivotItems("B").Visible = False
        .PivotItems("C").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Project Status"). _
        EnableMultiplePageItems = True

Both CurrentPage and PivotItems seem to be the source of the problem, my vba skills are very limited and i am unable to debug the problem.

Thanks. Appreciate the help.

Is the Project Status Field a Report Filter or a Row Label (or Column Label). The code is a bit different depending on which. — Scott Holtzman
@ScottHoltzman: In this particular case, it is a report filter, but i also have a column filter. I am assuming the difference between report, row and column is not too big. — Raj

Raj Raj · Accepted Answer · 2016-07-14T13:28:18

I figured out the problem and the solution is to include the filters while assigning an orientation using the following command:

objField.PivotItems("XXX").Visible = False

enter link description here

I found the logic from the below link.

Selecting multiple items in excel pivot table filter

1 Answers