Speeding up VBA Code that Sets Pivot Table Filters I've written out some VBA code that sets various filters onto pivottables. The problem I am experiencing is that it is extremely slow and bogs down my system heavily.

I've tried implementing various things to speed up the code, but nothing really has that much of an effect.

The reason I am doing it this way is the filters need to be dynamic and I am using multiple data sources, so just using a single slicer doesn't work in my case.

Here is my code:

 Private Sub Worksheet_Calculate()

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.DisplayStatusBar = False
    ActiveSheet.DisplayPageBreaks = False


    Dim DivRef, RegRef, DistRef, ZoneRef As String
    DivRef = Sheet5.Range("AH6").Value
    RegRef = Sheet5.Range("AH7").Value
    DistRef = Sheet5.Range("AH8").Value
    ZoneRef = Sheet5.Range("AN4").Value

With Sheet5.PivotTables("PivotTable21")
    .ManualUpdate = True
    .PivotFields("Division2").CurrentPage = DivRef
    .PivotFields("Region2").CurrentPage = RegRef
    .PivotFields("District2").CurrentPage = DistRef
    .ManualUpdate = False
End With


With Sheet5.PivotTables("PivotTable9")
    .ManualUpdate = True
    .PivotFields("Division2").CurrentPage = DivRef
    .PivotFields("Region2").CurrentPage = RegRef
    .PivotFields("District2").CurrentPage = DistRef
    .ManualUpdate = False
  End With

     Application.EnableEvents = True
     Application.ScreenUpdating = True
     Application.Calculation = xlCalculationAutomatic

    End Sub


In reality, there are a number of pivottables that are being filtered, but for the sake of this post, I am only posting PivotTable21 and PivotTable9's filters, though it is all more or less the same code.

I've been told that part of the problem might be that I am using calculated fields in my pivottables, and that may bog down some of the code as well.

Does anyone have any tips for speeding this code up?
I find that when you set PivotTable.ManualUpdate = True, Excel seems to often set that back to False for many of the changes you can make to PivotTables.  It does depend on the change, but I think changing PivotFields is one of those things that triggers this.

So you could try going to every PivotField change and resetting ManualUpdate to True after it.  Such as this:

With Sheet5.PivotTables("PivotTable21")
    .ManualUpdate = True
    .PivotFields("Division2").CurrentPage = DivRef
    .ManualUpdate = True
    .PivotFields("Region2").CurrentPage = RegRef
    .ManualUpdate = True
    .PivotFields("District2").CurrentPage = DistRef
    .ManualUpdate = False
End With


I know it seems silly, but I've seen this work.