267
Using Advanced Pivot Table Techniques
‘ Ensure that we get zeros instead of blanks in the data area
PT.NullString = “0”
‘ Calc the pivot table to allow the date label to be drawn
PT.ManualUpdate = False
PT.ManualUpdate = True
WSD.Activate
‘ Group Date by Week.
‘Figure out the first Monday before the minimum date
FirstDate = Application.Min(PT.PivotFields(“InvoiceDate”).DataRange)
WhichDay = Application.WorksheetFunction.Weekday(FirstDate, 3)
StartDate = FirstDate - WhichDay
PT.PivotFields(“InvoiceDate”).LabelRange.Group _
Start:=StartDate, End:=True, By:=7, _
Periods:=Array(False, False, False, True, False, False, False)
‘ Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
WSD.Activate
Range(“R1”).Select
End Sub
Using Advanced Pivot Table Techniques
You may be a pivot table pro and never have run into some of the really advanced tech-
niques available with pivot tables. The following sections discuss such techniques.
Using AutoShow to Produce Executive Overviews
If you are designing an executive dashboard utility, you might want to spotlight the top five
markets.
As with the AutoSort option, you could be a pivot table pro and never have stumbled across
the AutoShow feature in Excel. This setting lets you select either the top or bottom n
records based on any data field in the report.
The code to use AutoShow in VBA uses the
.AutoShow method:
‘ Show only the top 5 Markets
PT.PivotFields(“Market”).AutoShow Top:=xlAutomatic, Range:=xlTop, _
Count:=5, Field:= “Sum of Revenue”
When you create a report using the .AutoShow method, it is often helpful to copy the data
and then go back to the original pivot report to get the totals for all markets. In the follow-
ing code, this is achieved by removing the Market field from the pivot table and copying the
grand total to the report. Listing 11.8 produces the report shown in Figure 11.19.
11
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 267
To Next Page
Loading...
Need help?
General
