This is Part 3 of a project where I attempt to create a macro to automatically convert a regular pivot table to formulas; similar to the way you can convert an OLAP pivot table to CUBE formulas. So far, I was able to create a macro that outputs Cell Referenced GETPIVOTDATA formulas for the target pivot table. My last objective is to see if I can bring the Page Fields along for the ride. That is to say, I would like to auto-create slicers to interactively alter the values returned by the GETPIVOTDATA formulas.
The screen capture below shows how Page Filters can be converted to Slicers that work with the auto-generated GETPIVOTDATA formulas.
You can try it for yourself a sample file.
All the code for the macro is documented in the sample file, so I won’t bore you with the details. I will say that the tricky part of dynamically creating slicers is avoiding duplicates. Excel seems to freak out if you create a duplicate slicer. So each time the macro is run, we need to check for existing slicers. If existing slicers exists, we copy the slicer and point the copy to our pivot table. Otherwise we can create a new slicer with no issues.
By the way, I need to mention that I made heavy use of the excellent set of articles from Jan Karel Pieterse to get a head start on programming slicers. Check out Jan Karel’s site for all kinds of useful brain candy.
Well that’s it for this little side project of mine.
I would say that this macro has moderate usability. If you need a dashboard model fast, you can create a pivot table, run this macro, and voila! You’ve got yourself a set of formulas that can be moved, copied, and adjusted to create an interactive reporting model pretty quickly.