Excel/Amended: VBA events problem: slicer and unlinked PivotTable
I have a slicer which cannot for some reason see a new PivotTable i have added to a new sheet. Though the source is the same.
Instead, i have set up a Worksheet_PivotTableUpdate event on a PivotTable linked to the slicer (which does update), which then fires a macro (Matchpvtityerm) to update my new PivotTable to match the field and pivotitem selections.
For some reason, sometimes it doesn't update my new pivot unless F9 is pressed or the slicer selection pressed again. So i set up a Worksheet_Deactivate event on the slicer sheet to fire the same macro to ensure my new pivot updated.
However, i actually want to avoid using a Worksheet_Deactivate event as the user may wish to remain in the slicer sheet.
Additionally, there is a macro which adds formulas adjacent to the new pivotTable using a LastRow calculation to determine when to stop adding formulas.
The range containing these formulas is cleared each time a new Slicer selection is made or data is added to the Extract sheet or when the Worksheet_Deactivate event is triggered. Then the formulas macro to add formulas adds the formulas in to match the row count of pivot.
I cannot seem to set this up to run without actually going to the namedrange for formula clearing 'ClearData', so once again the user ends up in a different sheet from the slicer sheet.
I would like the PivotTable1 to consistently update when the slicer in 'AE Performance All Types' is updated and for the user to be able to remain in this sheet.
I appreciate my explanation may be a little abstract. I have the workbook with code that i can send upon request.
I have also asked another expert, who kindly suggested i also ask around; as this is a somewhat complex situation and i don't know who might have the most experience in this area.
Many thanks in advance,
ANSWER: Hi Quentin,
Have you checked out my pages on slicers already?
---------- FOLLOW-UP ----------
QUESTION: Thanks. I have had a look but perhaps i am not advanced enough to understand all the detail. The slicer in the workbook does not show my pivotTable despite being from same source range. I am unsure how to make the pivotTable link to the slicer if the slicer cannot 'see it' in report connections.
Perhaps this case something to do with the slicer caches referenced in your pages? If so, i am still unsure how to resolve the problem.
Any thoughts gratefully received.
Key is that "SOurce" is not about the Data itself, but rather about using the same Pivot Cache. If you create two pivottables, each having their own pivot cache then you cannot join them with the same slicer.
Click on one of the two pivots and press Alt+d,p to open the pt wizard.
Then click Back twice and select the option "Another pivottable report or pivotchart report".
Then click Next and select the other one and click finish. Now you should be able to tie the slicer to both tables.