MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Manipulating PivotTables programmatically ?

Posted by Steven on December 12, 2001 10:01 PM


This is just a general question about PivotTables and VBA procedures.

Under what circumstances would it be useful to manipulate PivotTables with VBA ? The options wich are provided to rearrange the data, format, hide, group, slice etc are numerous. Also building the table with VBA seems to be useless given the wizard we have....

Any comments are welcome, to convince me that unstanding pivottables from a VBA point of view does make sense (maybe with examples, references to websites etc).


Posted by Craig on December 13, 2001 6:47 AM

If you are happy doing things manually with the wizard then there is no reason to use VBA. There are situations were it can be beneficial however. For example, in VBA I take data extracted from another product, create 1 master pivot table from it (with multiple page fields) then split out all the combinations of page values (via "ShowPages") and create a separate graph for each new page. I can't do that manually using just the master pivot table because when I change the page values then any charts created from it will also change and to create all the different pivot tables and charts manually would take hours. It would depend on what you need to do and whether VBA would make sense to use in that situation.