Unable to set the Visible property of the PivotItem class (VBA)

Question

I am trying to manipulate an Excel 2007 Pivot Table trough VBA so I can loop trough the categories of the pivot table, set all to invisible but one, save the sheet as pdf and continue to the next category. For this I use the following piece of code.

Dim pf As PivotField
Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("NAME")

Dim pi as PivotItem
For Each pi In pf.PivotItems

    If pi.Visible = False Then
        pi.Visible = True 'Error here
    End If

    Dim pi2 As PivotItem
    For Each pi2 In pf.PivotItems
        If pi2 <> pi Then
            pi2.Visible = False
        End If
    Next pi2

    'Saving to PDF goes here
Next pi

The loop seems to be working the first time. Every category gets deselected but the first and it outputs a nice PDF file. The next time it enters the loop however it gives an 'Unable to set the Visible property of the PivotItem class' error at the indicated line. I am aware of the fact that in a pivot table there has to be at least one item selected but that is not the problem here as I am trying to set the visibility to TRUE instead of FALSE.

I tried fixing it by putting a check around it as maybe you are not allowed to set an already visible PivotItem to visible but that did not seem to work.

Any help would be very much appreciated!

Answer 1

This is due to the Pivot table using the cached pivot items instead of the current one. Make sure the table does not retain any old items. To do so, right click on your pivot table, click on Data tab and set "Number of itesm to retain per field" to "None". The code to do so in VBA is:

Dim pt As PivotTable

pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

Answer 2

I realize this is old, but wanted to contribute for those looking for a solution in the future.

I experienced this same error, solution I came up with was to just refresh the pivottable before beginning your pivotitem loop.

Try the following line of code:

ActiveSheet.PivotTables("PivotTable1").RefreshTable