275
Using Advanced Pivot Table Techniques
WSR.Cells(LastRow, 1).Value = “Top 5 Total”
‘ Do some basic formatting
‘ Autofit columns, bold the headings, right-align
WSR.Range(WSR.Range(“A2”), WSR.Cells(LastRow, 8)).Columns.AutoFit
Range(“A3”).EntireRow.Font.Bold = True
Range(“A3”).EntireRow.HorizontalAlignment = xlRight
Range(“A3”).HorizontalAlignment = xlLeft
Range(“B3”).Value = “Revenue”
Range(“A2”).Select
Next PivItem
‘ Clear the pivot table
PT.TableRange2.Clear
Set PTCache = Nothing
MsgBox Ctr & “ Region reports have been created”
End Sub
Manually Filtering 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, you can 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 never to set all items to False; otherwise, the macro ends with
an error. This tends to happen more than you would expect. An application may first show
products A and B and 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, no products will be visible along the
PivotField, which causes an error. To correct this, always loop through all PivotItems, mak-
ing 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
Product in the page field, loop
through to change the
Visible property to show only the total of certain products:
‘ Make sure all PivotItems along line are visible
For Each PivItem In _
PT.PivotFields(“Product”).PivotItems
PivItem.Visible = True
Next PivItem
‘ Now - loop through and keep only certain items visible
For Each PivItem In _
PT.PivotFields(“Product”).PivotItems
Select Case PivItem.Name
Case “Landscaping/Grounds Care”, _
“Green Plants and Foliage Care”
11
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 275
To Next Page
Loading...
Need help?
General
