Pivot Tables in VBA

Hi Bill

I have done some Pivot Table VBA in the past and yes they do recalculate as you manipulate them. To stop this you need to set ManualUpdate to True then back to False on completion. Screenupdating should also be turned off.

Sub SpeedUp()
Dim Pt As PivotTable

Set Pt = Sheet4.PivotTables(1)
With Pt
Application.ScreenUpdating = False
.ManualUpdate = True
'< Your Code >
.ManualUpdate = False
Application.ScreenUpdating = True
End With
End Sub

Great tool though aren't they ?

Dave

OzGrid Business Applications

Dave,
Thanks for the tip! I will add the manual updating logic to my code. The Pivot Tables worked out great. From a 10,000 row recordset, the code calculates 5 pivot tables in a blink.

Thanks for the info.

Bill

:: I am building my first set of pivot tables in a production application in VBA.
:: Questions:
:: 1) Creation of the pivot table takes several steps. First, there is the PivotCaches.add().CreatePivotTable, then I set some table settings, add fields, add data fields, and limit the PageField to a particular value. Is Excel re-calculating the pivot table after each command? Is there a way to turn this off until I get the whole pivot table defined in order to speed macro execution? I have done some Pivot Table VBA in the past and yes they do recalculate as you manipulate them. To stop this you need to set ManualUpdate to True then back to False on completion. Screenupdating should also be turned off. Dim Pt As PivotTable With Pt Application.ScreenUpdating = False .ManualUpdate = True '< Your Code > .ManualUpdate = False Application.ScreenUpdating = True End With End Sub

Great tool though aren't they ?