set the Pivot field “company code” always to True (Visible) whatever the value/change of my pivotItems

Question

I have small macro working with pivot table and I would like to set my field PivotItems related to company code always to true (Visible) whatever the value of my PivotItems. This code below works only for Pivot Items X,Y,X1,X2,Y2,Y4 and “blank”… However if I want to use this macro when my pivotItems changed, this macro will not work… I would like to enlarge my macro in order that it works also if the value of my pivotItems changes, it means all my value of Pivotfields("Company Code") should always be set to true to the parameter Visible whatever the changes of my PivotItems. If Someone could help me, that would be super. Thanks in advance. Xavi

Sub Activateallcompanycodefield()
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Company Code").CurrentPage _
        = "(All)"
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Company Code")
.PivotItems("X").Visible = True
.PivotItems("Y").Visible = True
.PivotItems("X1").Visible = True
.PivotItems("X2").Visible = True
.PivotItems("Y2").Visible = True
.PivotItems("Y4").Visible = True
.PivotItems("(blank)").Visible = True
    End With
End Sub

Maybe this helps also: "How to show every PivotItem except blank ones" stackoverflow.com/a/54420639/10908769 — Asger

Xavi Xavi · Accepted Answer · 2019-01-22T14:46:01

Finally I found the reply through this link: PivotTable how to set all items in filter to false?

So my problem is solved! I have just updated it to my VBA code as below:

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Company Code")
For i = 1 To .PivotItems.Count - 1
    .PivotItems(.PivotItems(i).Name).Visible = True
Next i
End With

set the Pivot field “company code” always to True (Visible) whatever the value/change of my pivotItems

1 Answers