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