Printing reports for selected budget holders

Question

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:

1. Set Application.Calculation = xlCalculationAutomatic

2. Looped the worksheets and set individual pivottables to .ManualUpdate = False

3. Paste Special Values the source data supplying the pivot cache to remove formulae and reduce size of file

4. Removed all the other slicers except the budget holder one

5. Removed all non essential sheets and pivot tables

6. 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

Answer 1

I have found a workaround by working with one of the pivot tables rather than with the slicer. Because the tables are all connected (i.e. all have the budget holder as a filter field and are connected via the slicer), when the budget holder is updated in the PivotField in the Pivot Table, it will update all the pivot tables with the same PivotField value.

So the code to replace the slicer code in original problem is simply:

With sheets ("BudgetHolder").PivotTables("PivotTable1").PivotFields("BudgetHolder")
    .ClearAllFilters
    .CurrentPage=Budholder
End With

So full code is:

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 all Pivot Tables to current budget holder from selection from Table

        With sheets("BudgetHolder").PivotTables("PivotTable1").PivotFields("BudgetHolder")
            .ClearAllFilters
            .CurrentPage=Budholder
        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