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, 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.
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.
Please could someone review the code and give suggestions on how to re-write my code to achieve the stated objective? 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_Holderbeing 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
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