Amazon VBA User Manual

Creating a group with VBA is a bit quirky. The .Group method can be applied to only a sin-
gle cell in the pivot table, and that cell must contain a date or the Date field label. This is
the first example in this chapter where you must allow VBA to calculate an intermediate
pivot table result.
You must define a pivot table with Invoice Date in the row field. Turn off
ManualCalculation to allow the Date field to be drawn. You can then use the LabelRange
property to locate the date label and group from there. Figure 11.17 shows the result of
Listing 11.6.
Chapter 11 Using VBA to Create Pivot Tables
264
11
Figure 11.17
The In Balance Date field
is now composed of
three fields in the pivot
table,representing year,
quarter, and month.
Listing 11.6 Code That Uses the Group Feature to Roll Daily Dates Up to Monthly Dates
Sub ReportByMonth()
‘ Listing 11.6
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets(“PivotTable”)
Dim WSR As Worksheet
‘ Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
WSD.Range(“R1:AZ1”).EntireColumn.Clear
‘ Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
‘ Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:=”PivotTable1”)
‘ Turn off updating while building the table
PT.ManualUpdate = True
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 264

Amazon VBA Specifications

General

Brand	Amazon
Model	VBA
Category	Software
Language	English