a guest Dec 8th, 2017 80 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
- 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.
- 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.
- 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?
- <insert code>……..
RAW Paste Data