Microsoft Office Tutorials and References
In Depth Information
Creating a More Complex Pivot Table
The code also could be more general through the use of indices rather than literal
strings for the PivotFields collections. This way, if the user changes the column
headings, the code will still work. For example, more general code would use
PivotFields(1) rather than PivotFields(‘Region’) .
As always, the best way to master this topic is to record your actions within a macro to find out
its relevant objects, methods, and properties. Then study the Help topics to understand how
everything fits together. In almost every case, you’ll need to modify the recorded macros. Or,
after you understand how to work with pivot tables, you can write code from scratch and avoid
the macro recorder.
Creating a More Complex Pivot Table
In this section, I present VBA code to create a relatively complex pivot table.
Figure 17-3 shows part of a large worksheet table. This table has 15,840 rows and consists of
hierarchical budget data for a corporation. The corporation has five divisions, and each division
contains 11 departments. Each department has four budget categories, and each budget category
contains several budget items. Budgeted and actual amounts are included for each of the 12
months. The goal is to summarize this information with a pivot table.
Figure 17-3: The data in this workbook will be summarized in a pivot table.
Search JabSto ::

Custom Search