J Despite the name of this feature being Top Autoshowyou can actually highlight the bottom or the top or any conceivable combination of top or bottom values

The code to use Top 10 Autoshow in VBA uses the .AutoShow method. 1 Show only the top 6 customers

PT.PivotFields("Customer").AutoShow Top:=xlAutomatic, Range:=xlTop, _ Count:=6, Field:="Total 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 customers. In the following code, this is achieved by removing the customer from the pivot table and copying the grand total to the report. The following code below produces the report shown in Figure 12.35:

Sub Top6CEOReport()

' Produce a report of top 6 customers with

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")

' 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" ' Define Calculated Fields

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

' Set up the data fields With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 1

0 0

Post a comment