273
Using Advanced Pivot Table Techniques
PT.TableRange2.Offset(3, 0)
PT.TableRange1.Offset(1, 0)
Which you use is your preference, but if you use TableRange2, you won’t have problems
when you try to delete the pivot table with
PT.TableRange2.Clear. If you were to acciden-
tally attempt to clear
TableRange1 when there are page fields, you would end up with the
dreaded “Cannot move or change part of a pivot table” error.
Listing 11.10 produces a new workbook for each region, as shown in Figure 11.21.
11
Figure 11.21
By looping through all
items found in the
Region page field,the
macro produced one
workbook for each
regional manager.
continues
Listing 11.10 Code That Creates a New Workbook per Region
Sub Top5ByRegionReport()
‘ Listing 11.10
‘ Produce a report of top 5 customers for each region
Dim WSD As Worksheet
Dim WSR As Worksheet
Dim WBN As Workbook
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets(“PivotTable”)
‘ 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”)
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 273
To Next Page
Loading...
Need help?
General
