Microsoft Excel

Manually Filter Two or More Items in a PivotField

In addition to setting up a calculated pivot item to display the total of a couple of products that make up a dimension, it is possible to manually filter a particular PivotField.

For example, you have one client who sells shoes. In the report showing sales of sandals, he wants to see just the stores that are in warm-weather states. The code to hide a particular store is

PT.PivotFields("Store").PivotItems("Minneapolis").Visible = False

You need to be very careful to never set all items to False; otherwise, the macro will end with an error. This tends to happen more than you would expect. An application may first show products A and B, then on the next loop show products C and D. If you attempt to make A and B not visible before making C and D visible, you will be in the situation of having no products visible along the PivotField, which causes an error. To correct this, always loop through all PivotItems, making sure to turn them back to Visible before the second pass through the loop.

This process is easy in VBA. After building the table with Line of Business in the page field, loop through to change the Visible property to show only the total of certain products. Use the following code:

    ' Make sure all PivotItems along line are visible
    For Each PivItem In _
        PT.PivotFields("Line of Business").PivotItems
        PivItem.Visible = True
    Next PivItem

    ' Now - loop through and keep only certain items visible
    For Each PivItem In _
        PT.PivotFields("Line of Business").PivotItems
        Select Case PivItem.Name
            Case "Copier Sale", "Printer Sale"
                PivItem.Visible = True
            Case Else
                PivItem.Visible = False
        End Select
    Next PivItem