If you ever iterate over PivotItems, set the PivotTable's .ManualUpdate to TRUE while you make your changes, and set it back to FALSE afterwards to avoid the PivotTable refreshing after each and every change. This will radically speed your code up.
If filtering on just one item, put hat field...
You can use the DataModel to add comments to PivotTables if you have Excel 2013 or later. See https://stackoverflow.com/questions/45132111/add-column-with-custom-text-to-pivot-table/45137050#45137050
I am working on a commercial add-in that does just this. Screenshots at the following StackOverflow links:
http://stackoverflow.com/questions/38904866/excel-pivot-table-filters-how-to-make-one-filter-drop-down-dependent-on-anothe/38911606#38911606...
Hi TyWils. No, I haven't (yet) progressed this, because I've been working on some other PivotTable-based addins that I want to get out the door first. But this is on my radar, and I would be interested in talking further about what it is you are trying to achieve, because there's nothing like a...
Are all these PivotTables based on the same raw data? If so, then if you have Excel 2010 or later you can just add a Slicer for the field concerned, and connect that Slicer to all the other PivotTables. Any changes you make in one field are then automatically replicated in all the others.
@NARAYANK991 Note that one offset formula alone can cause issues, if it is at the head of a large-enough dependency tree, as per my article at http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/
Late contribution, but if you ensure that the outline column is entered a text (and not general format) AND each sub part of the outline has the same amount of digits in it, then you can sort on it, as outlined at this post...
This kind of thing indeed takes a lot of work. I've been working on an 'improved' slicer for over a year, that only appears when you double-click a PivotTable, and that lets you do all sort of wildcard filtering that you can't do with native Slicers/Pivots:
@Manosh Here's a guest post I wrote some time back on the Chandoo forum where the named ranges are updated automatically as per your comment above.
http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/
YoungSparky: having formulas that reference other workbooks is usually a recipe for disaster. In this case, it won't actually work unless both workbooks are open, because INDIRECT can't retrieve data from closed workbooks. Nor will INDEX, which is another function I often use to make cascading...