Info

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

1 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 Ctr = 0

' Create a new blank workbook with one worksheet

Set WBN = Workbooks.Add(xlWBATWorksheet)

Set WSR = WBN.Worksheets(l)

WSR.Name = "Report"

' Set up Title for Report

.Value = "Sales by Region - Products ABC & DEF Only"

.Font.Size = 14 End With

' Copy the pivot table data to row 3 of the report sheet 1 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, 1).Value = "Division Total"

' Do some basic formatting

1 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

' Clear the Pivot Table PT.TableRange2.Clear Set PTCache = Nothing

MsgBox "ABC/DEF Division report has been created"

End Sub

Figure 12.41

VBA makes it fairly easy to exploit the filtered page field anomaly and build a pivot report for two products at one time.

Sales by Region - Products ABC & DEF Only

Reilio H

RevenueGioss Ptofit GP.à

Central

33,955« 1 a,91 OK 55.7%

East

35,091K 19.5Ë6K 55.0%

We si

24.7B4K 13,687« 55.2%

Division Total

93.032K 52.163K 55.6%

0 0

Post a comment