Microsoft Excel —

New Workbook to Hold the Report

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. At the top of the procedure, add these statements:

Dim WSR As Worksheet
Dim WBO As Workbook
Dim WBN As Workbook
Set WBO = ActiveWorkbook
Set WSD = Worksheets("Pivot Table")

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

' Create a New Blank Workbook with one Worksheet
Set WBN = Workbooks.Add(xlWorksheet)
Set WSR = WBN.Worksheets(1)
WSR.Name = "Report"
' Set up Title for Report
With WSR.Range("A1")
    .Value = "Revenue by Market and Year"
    .Font.Size = 14
End With