Well here’s something that I probably learned once, but have subsequently forgotten and then rediscovered: You can filter a PivotTable Page Field just by typing the PivotItem that you want to filter it on. So if I start with a PivotTable like this:
And say I don’t like the look of the mysterious substance that the kids have left smeared all over the mouse (probably just jam, but who the hell knows). I desperately want to filter that PivotField, but I desperately want to avoid the mouse. Well, watch what happens if I ust overtype the (All) in the PageField with the thing I want to filter by:
BING!
What’s more, if I type the name of a field that’s not already in the PivotTable over the existing PageField name:
…then Excel does something else intelligent: it says “Oh, you want me to bring that PageField into the Pivottable for you.”
The same thing in terms of adding new Fields goes for RowFields:
If you think about it, the adding of the fields is the same behavior as simply overtyping fields already in the PivotTable to rearrange them.
Regardless, now that this secret’s out, I don’t have to chip the sticky crap from my Mouse. As Phyllis Diller once said…Housework can’t kill you, but why take a chance? No, I don’t know who she is, either.
And here’s something else that you might want to know about these behaviors: they don’t trigger the PivotTableUpdate event. Which is a problem. Because that means that your VBA routines won’t catch 100% of changes to a PivotTable’s structure by using the PivotTableUpdate event alone…you’ll also have to hook into the SheetChange event and check if the cell that just changed happened to be a PivotField. Tedious.
*Sigh*. After more looking at the implications of this, I realize that given that refreshing a PivotTable triggers first a SheetChange event and then a PivotTableUpdate event, you’ll have to write that ‘backup’ SheetChange event code so that it does NOT execute if triggered by a PivotTable refresh. Which is tricky, because the SheetChange event gets called first. Only way I can think of doing this is to check the undo stack, and see if it says “PivotTable Refresh”. Which is fine for English installs.
So now my app only works in English. Great!
Edit: One way you can ignore the SheetChange if triggered by a pivot refresh is like so:
' Was the event triggered by a change to a PivotTable LabelRange? On Error Resume Next Set pf = Target.PivotField Set pt = pf.Parent On Error GoTo 0 If Not pf Is Nothing Then If Target.Address = pf.LabelRange.Address Then ...This works because if a refresh triggered the SheetChange, then the Target would be the entire Pivottable.TableRange2 address.
Thanks Jeff
I’ve always hated the way report filter looks and the position of it…Now I can just hide that row, create a active x drop down somewhere else and have it change the filter value.
Jay: You might as well have the ActiveX dropdown control the Pivot directly. Even better, either use a Slicer, or another Pivot ‘pretending’ to be a data validation dropdown, as I outline at http://dailydoseofexcel.com/archives/2014/08/16/sync-pivots-from-dropdown/
It’s a good reminder. It works if the data source is in Excel, and doesn’t go via PowerPivot/SQL.
(All) = Works. All = Doesn’t work + changes the dimension name.
A couple of new things here for me that I am happy to learn about. When I try to change/add to the row fields, all that happens is that it changes the name from Row Labels to whatever I type, for example.
I am running Windows 10 and Office365/Excel 2016 if that helps!