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

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

1 Go back to the pivot table to get totals without the AutoShow PT.PivotFields("Customer").Orientation = xlHidden PT.ManualUpdate = False PT.ManualUpdate = True PT.TableRange2.Offset(2, 0).Copy

WSR.Cells(LastRow + 2, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats WSR.Cells(LastRow + 2, 1).Value = "Total Company"

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

1 Do some basic formatting

1 Autofit columns, bold the headings, right-align

WSR.Range(WSR.Range("A2"), WSR.Cells(LastRow + 2, 4)).Columns.AutoFit Range("A3").EntireRow.Font.Bold = True Range("A3").EntireRow.HorizontalAlignment = xlRight Range("A3").HorizontalAlignment = xlLeft Range("B3").Value = "Revenue"


MsgBox "CEO Report has been Created" End Sub

Figure 12.35

The Top 6 Customers report contains two pivot tables.


MsgBox "CEO Report has been Created" End Sub

Top 6 Customers

0 0

Post a comment