Microsoft Excel —

Summary on a Blank Report Worksheet

Imagine that you have submitted the pivot table in Figure 8, and your manager hates the borders, hates the title, and hates the words "Line of Business" in cell O2. You can solve all three of these problems by excluding the first row(s) of PT.TableRange2 from the .Copy method and then using PasteSpecial(xlPasteValuesAndNumberFormats) to copy the data to the report sheet.

NOTE

In Excel 2000 and earlier, xlPasteValuesAndNumberFormats was not available. You would have to Paste Special twice: once as xlPasteValues and once as xlPasteFormats.


In the current example, the .TableRange2 property includes only one row to eliminate, row 2, as shown in Figure 8. If you had a more complex pivot table with several column fields and/or one or more page fields, you would have to eliminate more than just the first row of the report. It helps to run your macro to this point, look at the result, and figure out how many rows you need to delete. You can effectively not copy these rows to the report by using the Offset property. Copy the TableRange2 property, offset by one row. Purists will note that this code does copy one extra blank row from below the pivot table, but this really does not matter, because the row is blank. After doing the copy, you can erase the original pivot table and destroy the pivot cache:

' 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. Range("A3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
PT.TableRange2.Clear
Set PTCache = Nothing

Note that you used the Paste Special option to paste just values and number formats. This gets rid of both borders and the pivot nature of the table. You might be tempted to use the All Except Borders option under Paste, but this keeps the data in a pivot table, and you won't be able to insert new rows in the middle of the data.