You are here:

Excel/Amended: VBA events problem: slicer and unlinked PivotTable

Advertisement


Question
QUESTION: Hi,

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,

Quentin

ANSWER: Hi Quentin,

Have you checked out my pages on slicers already?

http://www.jkp-ads.com/Articles/slicers00.asp

---------- 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.

Thanks,

Quentin

Answer
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.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jan Karel Pieterse

Expertise

Excel and Excel/VBA questions

Experience

Excel MVP

Organizations
Self employed Excel developer

Education/Credentials
Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2016 About.com. All rights reserved.