0

I am trying to remove some fields from a Pivot Table. The Table changes but the list of items I am trying to remove stay the same.

It works if I just use the deselect code like this:

Set pf =ActiveSheet.PivotTables("PivotTable1").PivotFields("ServiceName")
pf.PivotItems("Disk").Visible = False
pf.PivotItems("SNMP").Visible = False
pf.PivotItems("POP").Visible = False..... and so on for about 140 Items

My problem comes when I have a Pivot table that does not contain the PivotItem. Run time error: "Unable to get the PivotItems property of the PivotField class

So I thought I would us a simple IF THEN:

If pf = ("POP") Then pf.PivotItems("POP").Visible = False

But it doesn't work. It doesn't do anything, no errors, no changes to the field. It just blinks and its done.

What am I missing?

Community
  • 1
  • 1
Skip
  • 1

1 Answers1

0

You could iterate over the PivotItems, therefore you modify only existing items, like so (see MSDN for a description): 

Dim dict As New Scripting.Dictionary 

' add the items to be removed
' dictionaries take key and value, but we are 
' only interested in the key here. So the
' value could really be anything.
c.Add "name1" 1
c.Add "name2" 1

Worksheets("sheet4").Activate 
With Worksheets("sheet3").PivotTables(1) 
 For i = 1 To .PivotFields.Count 
   For j = 1 To .PivotFields(i).PivotItems.Count
     If c.Exists(.PivotFields(i).PivotItems(j).Name) then
       .PivotFields(i).PivotItems(j).Visible = False
     End If
   Next 
 Next 
End With
pintxo
  • 2,085
  • 14
  • 27
  • For each Pivot Table I work with there are about 30-40 items that I want to leave selected. Wouldn't this process un-select all of my fields? Thanks for the info, I am really new to VB so I am not entirely sure what I am doing. – Skip Jun 08 '16 at 13:53
  • You could create a dictionary with those values you need to change. See here how to use one: http://stackoverflow.com/a/915333/683977 – pintxo Jun 08 '16 at 19:46