I have tricked a pivot-table to search in value fields and item-fields like a manual auto-filter. I did this by applying a manual Autofilter on the Pivot Table by using this solution. Now I can filter all columns like a regular AutoFilter which simply hides the rows during a filter instead of removing them from the PivotTable. This is exactly what I want.
The problem is that it works through via user interface but not via VBA. The macro recorder shows Range.Autofilter code for filtering/unfiltering the data, but when I run that code again, it gives me Run-time error 1004 "AutoFilter method of Range class failed" because the underlying data is on a pivot-table.
Can we get this to work via VBA on pivot tables without creating a separate data reference to the Pivot and then apply manual AutoFilter on it?
The problem is that it works through via user interface but not via VBA. The macro recorder shows Range.Autofilter code for filtering/unfiltering the data, but when I run that code again, it gives me Run-time error 1004 "AutoFilter method of Range class failed" because the underlying data is on a pivot-table.
Can we get this to work via VBA on pivot tables without creating a separate data reference to the Pivot and then apply manual AutoFilter on it?
