SHARE
TWEET

Untitled

a guest Dec 8th, 2017 80 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Situation:
  2.  
  3. 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.
  4.  
  5. Objective:
  6.  
  7. 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.
  8.  
  9. Problem:
  10.  
  11. 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.
  12.  
  13. 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)
  14.  
  15. For Each SlicItem In .SlicerItems
  16.  
  17.                If BudHolders(x) <> SlicItem.Value Then
  18.  
  19.                    SlicItem.Selected = False
  20.  
  21.                End If
  22.  
  23.            Next SlicItem
  24.  
  25. The calculation "running slicer operation" takes about 0.5 seconds per budgetholder (if this is a specific message quote it exactly)
  26.  
  27. Attempts to improve speed:
  28.  
  29. 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.
  30.  
  31. For example i:
  32.  
  33. 1) Set Application.Calculation = xlCalculationAutomatic
  34. 2) Looped the worksheets and set individual pivottables to .ManualUpdate = False
  35. 3) ……………
  36.  
  37. This made minimal (?) improvements to execution speed.
  38.  
  39. 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.
  40.  
  41. Supporting info:
  42.  
  43. • There are 175 items (be specific i.e. is it 100 or 175?) in the slicer Slicer_Budget_Holder;
  44.  
  45. • The source data is about 80,000 lines, set in a table ("BudHolderList"), with several columns being calculated fields;
  46.  
  47. • There are 8 (specific number required) pivot tables, all copied from the first one I created, but using different fields and showing different views;
  48.  
  49. • 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;
  50.  
  51. • 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;
  52.  
  53. • Windows 8?
  54.  
  55. • Excel version 2016?
  56.  
  57. • 32/64 bit?
  58.  
  59. Code:
  60.  
  61. <insert code>……..
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top