Add Subtotals by Product.

Be sure to add a page break at each change in product

'Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=_ ' Array(3, 4, 5, 6), Replace:=True, PageBreaks:=True, _ 1 SummaryBelowData:=True

1 The above code would fail if there were no sales

1 for the West region on any given day. Build a dynamic

' list of which columns to total.

FinalCol = Cells(3, 255).End(xlToLeft).Column

ReDim Preserve TotColumns(1 To FinalCol - 2)

For i = 3 To FinalCol

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=TotColumns _ , Replace:=True, PageBreaks:=True, SummaryBelowData:=True

' Make sure the columns are wide enough for totals GrandRow = Range("A65536").End(xlUp).Row Cells(GrandRow, 3).Resize(1, 4).Columns.AutoFit Cells(GrandRow, 3).Resize(1, 4).NumberFormat = "#,##0,K" 1 Add a page break before the Grand Total row; otherwise ' the product manager for XYZ will have two totals WSR.HPageBreaks.Add Before:=Cells(GrandRow, 1)

' Change the heading in the final column to Total instead of Grand Total Cells(3, FinalCol).Value = "Total"

MsgBox "Product Report has been Created" End Sub

Figure 12.20 shows the report produced by this code.

Figure 12.20

It takes less than two seconds to convert 10,000 rows of transactional data to this useful report if you use the code that produced this example.Without pivot tables,the code would be far more complex.

Revenue by Product and Customer

0 0

Post a comment