Info

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

1 Show only the top 10 customers

PT.PivotFields("Customer").AutoShow Type:=xlAutomatic, Range:=xlTop, Count:=10, 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 Ctr = 0

1 Loop through each region

For Each PivItem In PT.PivotFields("Region").PivotItems Ctr = Ctr + 1

PT.PivotFields("Region").CurrentPage = Pivltem.Name 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 = Pivltem.Name

1 Set up Title for Report

.Value = "Top 10 Customers in the " & Pivltem.Name & " Region"

.Font.Size = 14 End With

1 Copy the pivot table data to row 3 of the report sheet ' Use offset to eliminate the page & title rows of the pivot table PT.TableRange2.Offset(3, 0).Copy

WSR.[A3].PasteSpecial Paste:=xlPasteValuesAndNumberFormats LastRow = WSR.Cells(65536, 1).End(xlUp).Row WSR.Cells(LastRow, l).Value = "Top 10 Total"

1 Do some basic formatting

' Autofit columns, bold the headings, right-align WSR.Range(WSR.Range("A3"), WSR.Cells(LastRow, 4)).Columns.AutoFit Range("A3").EntireRow.Font.Bold = True Range("A3").EntireRow.HorizontalAlignment = xlRight Range("A3").HorizontalAlignment = xlLeft Range("B3").Value = "Revenue"

Range("A2").Select

Next PivItem

1 Clear the pivot table PT.TableRange2.Clear Set PTCache = Nothing

MsgBox Ctr & " Region reports have been created"

End Sub

Figure 12.38

By looping through all items found in the Region page field, the macro produced one workbook for each regional manager.

Top 10 Customers in the West Rf

0 0

Post a comment