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.
On each loop I am having to apply
.ClearManualFilter to the slicer cache in between applying the current budget holder name (taken from the Budget Holder Table and held in a the
Budholder string variable); 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 the status bar says it ‘calculates and populates pivot table report’.
With SlicCache .ClearManualFilter For Each SlicItem In .SlicerItems If Budholder <> SlicItem.Value Then SlicItem.Selected = False End If Next SlicItem End With
Watching the budget holder list in the slicer as the macro runs, it takes about 0.5 seconds for each loop of the
For Each SlicItem In .Sliceritems 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:
Application.Calculation = xlCalculationAutomatic
Looped the worksheets and set individual pivottables to
.ManualUpdate = False
Paste Special Values the source data supplying the pivot cache to remove formulae and reduce size of file
Removed all the other slicers except the budget holder one
Removed all non essential sheets and pivot tables
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.
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. 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?
• 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
"BudHolderList", 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,
Slicer_Budget_Holder being the one that customises the pivots for each of the c.170 budget holders;
• The file size is 30Mb and saved as a
.xlsb. 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
Sub PrintPDFsSO() Dim Lobj As ListObject Dim Budholder As String Dim x As Long Dim SourceBk As Workbook Dim SlicItem As SlicerItem, SlicCache As SlicerCache Set SourceBk = ThisWorkbook Set Lobj = SourceBk.Sheets("BudHolders").ListObjects("BudHolderList") 'Budget Holders held in BudHolderList Table Set SlicCache = SourceBk.SlicerCaches("Slicer_Budget_Holder") For x = 1 To Lobj.DataBodyRange.Rows.Count 'Loop through Table to take each visible Budget Holder, one at a time If Not Lobj.DataBodyRange.Rows(x).EntireRow.Hidden Then Budholder = Lobj.DataBodyRange(x, 3) 'Name of budget holder held in 3rd column of Budget Holder Table 'Code to change filter in slicer to current budget holder from selection from Table With SlicCache .ClearManualFilter 'clears all filters and shows all items in budget holder slicer For Each SlicItem In .SlicerItems If Budholder <> SlicItem.Value Then 'Works down the slicer (which holds entire budget holder list - 170 budgetholders - from cache) and tests if the current value of budholder matches slicer item SlicItem.Selected = False 'Grinding to a virtual halt on this line as status bar says it 'calculates and populates pivot table report' End If Next SlicItem End With 'Use budholder name to populate some graphs etc in workbook with new figures SourceBk.Sheets("Graphs - Summary").Range("BudHolder_SG").Value = Budholder 'Do Calcs, Printing, saving etc End If Next End Sub
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!