Info

.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

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

1 Show only the top 6 customers

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

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

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

1 Create a new blank workbook with one worksheet

Set WBN = Workbooks.Add(xlWBATWorksheet)

Set WSR = WBN.Worksheets(l)

WSR.Name = "Report"

' Set up ritle for report

.Value = "Top 6 Customers" .Font.Size = 14 End With

0 0

Post a comment