Creating a New Workbook to Hold the Report

Let's say that you want to build the report in a new workbook so that it can be easily mailed to the product managers. This is fairly easy to do. To make the code more portable, assign object variables to the original workbook, the new workbook, and the first worksheet in the new workbook. This code is similar to the code used in the Chapter 11 case study. At the top of the procedure, add these statements:

Dim WSD As Worksheet

Dim WSR As Worksheet

Dim WBO As Workbook

Dim WBN As Workbook

Dim PTCache As PivotCache

Dim PT As PivotTable

Dim PRange As Range

Set WBO = ActiveWorkbook

Set WSD = Worksheets("Pivot Table")

After the pivot table has been successfully created, build a blank Report workbook with this code:

1 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 = "Revenue by Product and Customer" .Font.Size = 14 End With

0 0

Post a comment