Nsryjdtyk

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.

I think a problem you might have is that you have this in Worksheet_Calculate and you set your Application.Calculation to automatic -

Private Sub Worksheet_Calculate()

...

Application.Calculation = xlCalculationAutomatic

End Sub

You are applying all of these filters every time anything calculates on whatever sheet this is on. Do you need that? I doubt it - maybe set your sheet to manual calculation or move the event out of the calculate event and set up a button or something.

When you declare variables, you need to give them all a type -

Dim DivRef, RegRef, DistRef, ZoneRef As String

This declares DivRef, RegRef and DistRef as Variant and only ZoneRef as string. Variants can slow down your code, but I doubt that's really the problem here. It's good practice, however, to always type them. You need to type each one e.g.

Dim divRef as String, regRef as String, distRef as String, zoneRef as String

Or more appropriately-

Dim division As String

Dim region As String

Dim district As String

I don't see you using zone so I didn't include it.

I also changed the naming, you want your names to be clear and concise.