Advanced Filter report in the case study at the end of Chapter 11. The results of this macro are three new sheets. Figure 12.36 shows the first sheet created:

Sub RetrieveTop3CustomerDetail() Dim WSD As Worksheet 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"

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

1 Sort customers descending by sum of revenue PT.PivotFields("Customer").AutoSort Order:=xlDescending, _ Field:="Total Revenue"

' Show only the top 3 customers

PT.PivotFields("Customer").AutoShow Type:=xlAutomatic, _ Range:=xlTop, Count:=3, Field:="Total Revenue"

' Ensure that we get zeroes instead of blanks in the data area PT.NullString = "0"

' Calc the pivot table PT.ManualUpdate = False PT.ManualUpdate = True

' Produce summary reports for each customer For i = 1 To 3

PT.TableRange2.Offset(i + 1, 1).Resize(1, 1).ShowDetail = True ' The active sheet has changed to the new detail report

1 Add a title

Range("A1:A2").EntireRow.Insert Range("A1").Value = "Detail for " & _

PT.TableRange2.Offset(i + 1, 0).Resize(1, 1).Value & " (Customer Rank: " & i & ")"

Next i

MsgBox "Detail for Top 3 customers has been created" End Sub

Figure 12.36

Pivot table applications are incredibly diverse. This macro created a pivot table of the top three customers,then used the ShowDetail property to retrieve the records for each of those customers.

0 0

Post a comment