Selecting Multiple PivotItems - Slow

[Peter.Stevens2]

Hi Folks,

I'm having a nightmare trying to speed up some code I've written to select multiple page fields in Excel 2007 Pivot tables.

I have a workbook that has several sheets with 20+ pivottables on each that make up a dashboard of sorts, there is a user form that users can use to select filters on these pivot tables and the selected filters are used to apply to all pivot tables on the active worksheet. this saves them having to make the selections on each pivot table in turn.

I'd set this up so users could select 'all' pivot items in a partcular field or just select one, for a specific team etc. This works fine and refreshes the filter on all pivot tables in a couple of seconds. However I've had a request to make some of the pivot item selections 'multiselect'. I've done this by changing the Combobox on the userform into a multiselect Listbox which populates an array when the user apply's their selections. The Array is then looped through to make the pivot items visible. the problem is that the Pivot fields in question can have up to 300ish indivudual items and it takes aaaaaages for the loop to go through each of these pivot items and make it Visible/Hidden as applicable. I've tried using pt.ManualUpdate = True to no avail.

The source data for the pivots are in the same workbook and all pivot tables run off 1 or 2 pivot caches.

The code I'm having trouble with is below, any help to fix would be greatly appreciated! Thanks All

'loop through each pivottable and make correct page selections
For Each pt In ThisWorkbook.ActiveSheet.PivotTables
    pt.ManualUpdate = True
 
    Application.StatusBar = "Calculating - " & Round(Counter / ptCounter, 2) * 100 & "%"
 
    'if there are selected accounts then apply the filter
    If FilterAccount = True Then
 
        pt.PivotFields("Account").EnableMultiplePageItems = True
 
        For Each pi In pt.PivotFields("Account").PivotItems
            If pi <> "(blank)" Then pi.Visible = False
        Next pi
 
        For i = 1 To UBound(AccountArray, 1)
            pt.PivotFields("Account").PivotItems(AccountArray(i)).Visible = True
        Next i
 
        pt.PivotFields("Position").EnableMultiplePageItems = True
 
    'else select all accounts
    Else
 
        pt.PivotFields("Account").ClearAllFilters
        pt.PivotFields("Account").EnableMultiplePageItems = False
        pt.PivotFields("Account").CurrentPage = "(All)"
 
    End If

 

[Jerry Sullivan]

Hi Peter,

You can greatly reduce the time needed to filter for multiple PivotItems by testing whether each item is Visible first and only changing its state if it's currently incorrect.

There's a Function "Filter_PivotField" in this thread shows one way to do that.
The example in that thread is for setting one Page; however the Function is written to handle one or more PivotItems.

http://www.mrexcel.com/forum/showpost.php?p=2952507&postcount=2

 

[Peter.Stevens2]

Thanks Jerry, your function reduced the run time of the sub routine down from over 700 seconds to a more manageable 450ish.

Alas I think it's the sheer number of Tables, Fields and Items that need to be checked that is causing the routine to take so long. I think I'm going to leave it at that as this will only be a problem when a user chooses all of the filter options available, this will only be about 5% of the time. I've put a warning box when the problematic filters are selected to warn the user that the routine may take some time to run!

I appreciate your help!

Pete