Tutorial by Examples

One of the most powerful capabilities in Excel is the use of Pivot Tables to sort and analyze data. Using VBA to create and manipulate the Pivots is easier if you understand the relationship of Pivot Tables to Pivot Caches and how to reference and use the different parts of the Tables. At its most ...

These excellent reference sources provide descriptions and illustrations of the various ranges in Pivot Tables. References Referencing Pivot Table Ranges in VBA - from Jon Peltier's Tech Blog Referencing an Excel Pivot Table Range using VBA - from globaliconnect Excel VBA

Two important things to note when adding fields to a Pivot Table are Orientation and Position. Sometimes a developer may assume where a field is placed, so it's always clearer to explicitly define these parameters. These actions only affect the given Pivot Table, not the Pivot Cache. Dim thisPivot ...

This example changes/sets several formats in the data range area (DataBodyRange) of the given Pivot Table. All formattable parameters in a standard Range are available. Formatting the data only affects the Pivot Table itself, not the Pivot Cache. NOTE: the property is named TableStyle2 because the ...