trjhtr

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.