Situation:

I am trying to print off reports for selected budget holders (selected from a Budget Holder Table), using the budget holder name to feed into a slicer which then updates various pivot tables.

Objective:

The objective is to populate efficiently the slicer with a single budget holder at a time, taken from the filtered list in the table, swiftly produce the report pack, and move on to the next budget holder.

Problem:

I am having to apply .ClearManualFilter to the slicer cache in between applying different budget holder names (from an array holding the filtered list in the Budget Holder Table); and the applying of each individual budget holder requires that I do a comparison against every slicer item and set .Selected = False if the current SlicItem.Value <> current Budget Holder.

The current code works but it is grinding to a virtual halt on the following lines as it 'calculates and populates pivot table report'. (if this is a specific message quote it exactly)

For Each SlicItem In .SlicerItems

               If BudHolders(x) <> SlicItem.Value Then

                   SlicItem.Selected = False

               End If

           Next SlicItem

The calculation "running slicer operation" takes about 0.5 seconds per budgetholder (if this is a specific message quote it exactly)

Attempts to improve speed:

After reading the article here: https://powerpivotpro.com/2010/07/slicers-and-pivot-update-performance/ I tried implementing some code to speed up the report generation process.

For example i:

1) Set Application.Calculation = xlCalculationAutomatic
2) Looped the worksheets and set individual pivottables to .ManualUpdate = False
3) ……………

This made minimal (?) improvements to execution speed.

Please could someone review the code below and give suggestions on how to re-write my code to achieve the stated objective? I am happy to update the question with additional information as required.

Supporting info:

•	There are 175 items (be specific i.e. is it 100 or 175?) in the slicer Slicer_Budget_Holder;

•	The source data is about 80,000 lines, set in a table ("BudHolderList"), with several columns being calculated fields;

•	There are 8 (specific number required) pivot tables, all copied from the first one I created, but using different fields and showing different views;

•	There are 8 slicers (specific number required), most connected to all the pivot tables, Slicer_Budget_Holder being the one that customises the pivots for each of the c.100 budget holders;

•	The file size is 30Mb and saved as a .xlsb. I could value the source data to reduce the size to about 18Mb although i don't think that's the issue as the  source data is not re-calculating;

•	Windows 8?

•	Excel version 2016?

•	32/64 bit?

Code:

<insert code>……..