Excel Slicer Reset Filter Value Macro - Speed Issue

[CHRISBH]

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:

On each loop I am having to apply <code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">.ClearManualFilter</code> to the slicer cache in between applying the current budget holder name (taken from the Budget Holder Table and held in a the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Budholder</code> string variable); and the applying of each individual budget holder requires that I do a comparison against every slicer item and set <code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">.Selected = False</code> if the current <code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">SlicItem.Value <></code> current Budget Holder.
The current code works but it is grinding to a virtual halt on the following lines as the status bar says it 'calculates and populates pivot table report'.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; white-space: inherit;">
[/CODE]

[COLOR=#303336][FONT=inherit] 
With SlicCache

    [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]ClearManualFilter

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]For[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Each[/FONT][/COLOR][COLOR=#303336][FONT=inherit] SlicItem [/FONT][/COLOR][COLOR=#101094][FONT=inherit]In[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]SlicerItems

        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Budholder [/FONT][/COLOR][COLOR=#303336][FONT=inherit]<>[/FONT][/COLOR][COLOR=#303336][FONT=inherit] SlicItem[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

            SlicItem[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Selected [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]False[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Next[/FONT][/COLOR][COLOR=#303336][FONT=inherit] SlicItem

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR]</code>

Watching the budget holder list in the slicer as the macro runs, it takes about 0.5 seconds for each loop of the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">For Each SlicItem In .Sliceritems</code> loop to run, so with 170 budget holders in the slicer item list it takes about 3 minutes to run through the checks for one budget holder. It does speed up as the slicer increasingly contains a list of unmatched slicer items.

Attempts to improve speed:

After reading the article here: slicers and pivot update performance I tried implementing some code and workbook trimming to speed up the report generation process.
For example, I:

1. Set <code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Application.Calculation = xlCalculationManual</code>
2. Looped the worksheets and set individual pivottables to <code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">.ManualUpdate = False</code>
3. Paste Special Values the source data supplying the pivot cache to remove formulae and reduce size of file
4. Removed all the other slicers except the budget holder one
5. Removed all non essential sheets and pivot tables
6. Trimmed down the number of fields in the source data that the pivot Tables were picking up

This made barely noticeable improvements to execution speed.

Request:

Please could someone review the code below and give suggestions on how to re-write my code to achieve the stated objective?
I am hoping there is a way that rather than clearing all the filters and fully populating the slicer, then removing items one by one, the slicer can be set to filter on the single budget holder straight away, so avoiding the multiple processing operations that seem to take place every time the filter changes.
Alternatively, is there a way to prevent the filter from activating any calculations until it is set with the single budget holder, allowing it to run down the list checking much more quickly?

Supporting info:

• There are 170 budget holders in the source data feeding into the slicer
• The source data is about 80,000 lines, set in a table <code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">"BudHolderList"</code>, 34 columns, with 12 columns being calculated (mainly simple ColX & ColY type) fields;
• There are 12 pivot tables, all copied from the first one I created, but using different fields and showing different views;
• There are 7 slicers, most connected to all the pivot tables, <code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Slicer_Budget_Holder</code> being the one that customises the pivots for each of the c.170 budget holders;
• The file size is 30Mb and saved as a <code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">.xlsb</code>. I have tried paste special valuing the source data to remove all formulae and reduce the size to about 18Mb although i don't think that's the issue as I don't think the source data is re-calculating and repopulating the pivot tables;
• Windows 7 Professional
• Excel version 2010
• 64 bit

I work for UK local government and we are broke! No money to bring in consultants to help so relying on t'internet, goodwill and luck! Any help much appreciated.

 

[CHRISBH]

I have found a workaround by working with one of the pivot tables rather than with the slicer. Because the tables are all connected (i.e. all have the budget holder as a filter field and are connected via the slicer), when I the budget holder is updated in the PivotField in the Pivot Table, it will update all the either pivot tables with the same PivotField value.
So the code to replace the slicer code in original problem is simply:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; white-space: inherit;">With sheets ("BudgetHolder").PivotTables("PivotTable1").PivotFields("BudgetHolder")
.ClearAllFilters
.CurrentPage=Budholder
End With</code>