Moving the Summary to a Blank Report Worksheet

In Figure 12.16, I hate the borders. I hate the title. I hate the word "Region" in cell L2. You can solve all three of these problems by excluding the first row(s) of PT.TableRange2 from the .Copy and then using PasteSpecial(xlPasteValuesAndNumberFormats) to copy the data to the report sheet.

In the current example, the .TableRange2 property includes only one row to eliminate: Row 2 as shown in Figure 12.16. 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 1 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 PivotCache:

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

PT.TableRange2.Clear

Set PTCache = Nothing

Note that we 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.

0 0

Post a comment