To set up a page field in VBA, add the PageFields parameter to the AddFields method. The following line of code creates a pivot table with Region in the page field: PT.AddFields RowFields:="Customer", ColumnFields:="Data", PageFields:="Region"

The preceding line of code sets up the Region page field set to the value of (All), which returns all regions. To limit the report to just the East region, use the CurrentPage property: PT.PivotFields("Region").CurrentPage = "East"

One use of a page field is to build a userform where someone can select a particular region or a particular product. You then use this information to set the CurrentPage property and display the results of the userform.

Another interesting use is to loop through all Pivotltems and display them one at a time in the page field. You can quickly produce top 10 reports for each region using this method.

To determine how many regions are available in the data, use PT.PivotFields("Region").PivotItems.Count. Either of these loops would work:

For i = 1 To PT.PivotFields("Region").PivotItems.Count PT.PivotFields("Region").CurrentPage = _

PT.PivotFields("Region").PivotItems(i).Name PT.ManualUpdate = False PT.ManualUpdate = True Next i

For Each Pivltem In PT.PivotFields("Region").PivotItems PT.PivotFields("Region").CurrentPage = PivItem.Name PT.ManualUpdate = False PT.ManualUpdate = True Next PivItem

Of course, in both of these loops, the three region reports fly by too quickly to see. In practice, you would want to save each report while it is displayed.

So far in this chapter, we have been using PT.TableRange2 when copying the data from the pivot table. The TableRange2 property includes all rows of the pivot table, including the page fields. There is also a .TableRangel property, which excludes the page fields. In Figure 12.37, you can use either statement to get the rows starting with Customer:

PT.TableRange2.Offset(3, 0) PT.TableRange1.Offset(1, 0)

Which you use is your preference. I prefer to always use TableRange2, so that way when I try to delete the pivot table with PT.TableRange2.Clear, I never have any problems. If you were to accidentally 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.

The code that follows produces a new workbook for each region, as shown in Figure 12.38:

Sub Top10ByRegionReport()

1 Produce a report of top 10 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("Pivot Table")

1 Delete any prior pivot tables For Each PT In WSD.PivotTables

PT.TableRange2.Clear Next PT

' Define input area and set up a pivot cache FinalRow = WSD.Cells(65536, 1).End(xlUp).Row Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 8)

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)

Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Range("J2"),

TableName:="PivotTable1") PT.ManualUpdate = True ' Set up the row fields

PT.AddFields RowFields:="Customer", ColumnFields:="Data", _ PageFields:="Region"

' Define Calculated Fields

PT.CalculatedFields.Add Name:="GP_Pct", Formula:="=Profit/Revenue"

1 Set up the data fields With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 1 .NumberFormat = "#,##0,K" .Name = "Total Revenue" End With

With PT.PivotFields("Profit") .Orientation = xlDataField .Function = xlSum .Position = 2 .NumberFormat = "#,##0,K" .Name = "Gross Profit" End With

With PT.PivotFields("GP_Pct") .Orientation = xlDataField .Function = xlSum .Position = 3 .NumberFormat = "#0.0%" .Name = "GP%" End With

0 0

Post a comment