Microsoft Excel —

Suppress Grand Total for Rows

Because you are going to be using VBA code to add automatic subtotals, you can get rid of the Grand Total row. If you turn off Grand Total for Rows, you delete the column called Grand Total. Thus, to get rid of the Grand Total row, you must uncheck Grand Total for Columns. This is handled in the code with the following line:

PT.ColumnGrand = False

Handle Additional Annoyances

You've reached the end of the adjustments that you can make to the pivot table. To achieve the final report, you have to make the remaining adjustments after converting the pivot table to regular data.

Figure 8 shows the pivot table with all the adjustments described in the previous sections made and with PT.TableRange2 selected.

8. It took less than one second and 30 lines of code to get 90% of the way to the final report. To solve the last five annoying problems, you have to change this data from a pivot table to regular data.