CODEWITHSUNDEEP
vbaexcelexcel-2010pivot-table
Deepak
Deepak

Reputation: 473

Making multiple pivot items visible

I would like to make some pivot items visible and others invisible. But the problem is some items are not available in the pivotfields. The countries GE,KG,KZ,MN,AZ,TM will be avilable on next months.

But i would like to create a single code. If the items are present in the pivotfields then it must be visible orelse it must invisble. How can i change the code. Help me

Application.ScreenUpdating = False
      With ActiveSheet.PivotTables("MainTable").PivotFields("Country Code")
        For i = 1 To .PivotItems.Count
          .PivotItems(i).Visible = False
          .PivotItems("DE").Visible = True
        Next i
        .PivotItems("UA").Visible = True 'Available
        .PivotItems("BY").Visible = True 'Available
        .PivotItems("MD").Visible = True 'Available
        .PivotItems("GE").Visible = True 'Not available
        .PivotItems("KG").Visible = True 'Not available
        .PivotItems("KZ").Visible = True 'Not available
        .PivotItems("MN").Visible = True 'Not available
        .PivotItems("AZ").Visible = True 'Not available
        .PivotItems("TM").Visible = True 'Not available
        .PivotItems("DE").Visible = False
      End With
    Application.ScreenUpdating = True

Upvotes: 0

Views: 1114

Answers (1)

CLR
CLR

Reputation: 12279

I'd do this this way:

Application.ScreenUpdating = False
With ActiveSheet.PivotTables("MainTable").PivotFields("Country Code")
  For Each Pi In .PivotItems
    Pi.Visible = InStr(1, "UA,BY,MD,GE,KG,KZ,MN,AZ,TM", Pi.Name) > 0
  Next
End With
Application.ScreenUpdating = True

Instead of specifying a list of PivotItem names to make visible, why not scan through all available PivotItem's names and set their visibility based on their presence within a string..

Upvotes: 1

Related Questions