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 .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:
Set
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.
Request:
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?
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
"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