Excel VBA using arrays to update multiple pivot tables getting "unable to get PivotFields property of PivotTable class error msg RRS feed

  • Question

  • The variants are being populated with the correct data.  The correct worksheet is activated but the message appears on the first line referencing the pivot table.

    Any help would be greatly appreciated.

    Sub UpdateSAPCubePivotTables()

    Dim Record
    Dim WSName
    Dim PTName
    Dim NewYear
    Dim OldMonth
    Dim NewMonth
    Record = 1

    'Turns off automatic calculation

        With Application
            .ScreenUpdating = False
            .Calculation = xlManual
            .MaxChange = 0.001
        End With

    'Tells macro where to get values from Excel file

    WSName = Names("WSNames").RefersToRange.Value
    PTName = Names("PTName").RefersToRange.Value
    NewYear = Names("Year").RefersToRange.Value
    OldMonth = Names("OldMonth").RefersToRange.Value
    NewMonth = Names("NewMonth").RefersToRange.Value

    'Updating of SAP Cube Pivot Tables

    For Record = 1 To 47 Step 1

        Sheets(WSName((Record), 1)).Select
        ActiveSheet.PivotTables(PTName((Record), 1)).PivotFields("[Year].[Year].[Year]"). _
            VisibleItemsList = Array("[Year].[Year].[Year].&[NewYear]")
        ActiveSheet.PivotTables(PTName((Record), 1)).CubeFields("[Measures].[OldMonth]"). _
            Orientation = xlHidden
        ActiveSheet.PivotTables(PTName((Record), 1)).AddDataField ActiveSheet.PivotTables( _
            PTName((Record), 1)).CubeFields("[Measures].[NewMonth]")
       Next Record

    Thursday, April 20, 2017 8:23 PM

All replies

  • Hello,

    How do you create the PivotTable? According to PivotField.VisibleItemsList Property (Excel), This property is applicable to OLAP PivotTables only.

    Sorry that we are not familiar with OLAP PivotTables. I would suggest you create a sample file and share it here via OneDrive.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Friday, April 21, 2017 6:34 AM