Pivot Macro run slows on first click, after which it runs fast

[Alvino]

Hihi,


I have a combo box that fire a macro below successfully. However, i realised that when the user open the file, the first fire will be slow, around 5 seconds delay. After which, it response quite fast at spilt second.


Additional info:

1. I have around 6 of such macros in the same workbook.

2. The file size is arond 13mb.

3. The macros are placed in a module

4. I tried on old pc and latest pc, both carried the same behavior


Question: Why will Excel behave such? Is there a way to eliminate the delay?


Thanks for any insight.


Regards,

Alvino


Sub Pivot_GrpL1()


Dim PT_Top, PT_Growth, PT_Drop, PT_LR, PT_Overall_Prod, PT_Overall_LR, Budget_GWP, Budget_LR, AgentGWP As PivotTable


Set PT_Top = Sheets("Pivot").PivotTables("PT_Top")

Set PT_Growth = Sheets("Pivot").PivotTables("PT_Growth")

Set PT_Drop = Sheets("Pivot").PivotTables("PT_Drop")

Set PT_LR = Sheets("Pivot").PivotTables("PT_LR")

Set PT_Overall_Prod = Sheets("Pivot").PivotTables("Overall_Prod")

Set PT_Overall_LR = Sheets("Pivot").PivotTables("Overall_LR")

Set PT_Budget_GWP = Sheets("Pivot").PivotTables("Budget_GWP")

Set PT_Budget_LR = Sheets("Pivot").PivotTables("Budget_LR")

Set PT_AgentGWP = Sheets("Pivot").PivotTables("AgentGWP")


PT_Top.ManualUpdate = True

PT_Growth.ManualUpdate = True

PT_Drop.ManualUpdate = True

PT_LR.ManualUpdate = True

PT_Overall_Prod.ManualUpdate = True

PT_Overall_LR.ManualUpdate = True

PT_Budget_GWP.ManualUpdate = True

PT_Budget_LR.ManualUpdate = True

PT_AgentGWP.ManualUpdate = True


Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False


PT_Top.PivotFields("Group L1").CurrentPage = Range("GroupL1_Link").Value

PT_Growth.PivotFields("Group L1").CurrentPage = Range("GroupL1_Link").Value

PT_Drop.PivotFields("Group L1").CurrentPage = Range("GroupL1_Link").Value

PT_LR.PivotFields("Group L1").CurrentPage = Range("GroupL1_Link").Value

PT_Overall_Prod.PivotFields("Group L1").CurrentPage = Range("GroupL1_Link").Value

PT_Overall_LR.PivotFields("Group L1").CurrentPage = Range("GroupL1_Link").Value

PT_Budget_GWP.PivotFields("Group L1").CurrentPage = Range("GroupL1_Link").Value

PT_Budget_LR.PivotFields("Group L1").CurrentPage = Range("GroupL1_Link").Value

PT_AgentGWP.PivotFields("Group L1").CurrentPage = Range("GroupL1_Link").Value


PT_Top.ManualUpdate = False

PT_Growth.ManualUpdate = False

PT_Drop.ManualUpdate = False

PT_LR.ManualUpdate = False

PT_Overall_Prod.ManualUpdate = False

PT_Overall_LR.ManualUpdate = False

PT_Budget_GWP.ManualUpdate = False

PT_Budget_LR.ManualUpdate = False

PT_AgentGWP.ManualUpdate = False


Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True


Set PT_Top = Nothing

Set PT_Growth = Nothing

Set PT_Drop = Nothing

Set PT_LR = Nothing

Set PT_Overall_Prod = Nothing

Set PT_Overall_LR = Nothing

Set PT_Budget_GWP = Nothing

Set PT_Budget_LR = Nothing

Set PT_AgentGWP = Nothing


End Sub

 

[Hui]

Alvino

I would think the fact that your spreadsheet is largish, not huge and you have 9 Pivot Tables that the delay is caused by the Pivot Tables refreshing their caches.

Once refreshed updates will be a lot faster.


What to do about it,

Can the Pivot tables be generated as required (by VBA) instead of being left in the workbook all the time ?


Also look at replacing volatile functions with non-volatile functions in your workbook refer:

http://www.decisionmodels.com/calcsecretsi.htm

 

[Alvino]

Hi Hui,


Thanks for the insight


I need to have the pivot table ready in the workbook as it is linked to a dashboard for users.

Do you happen to know of anyway to load the caches on opening the workbook?


I don't have many formulas. Mainly index, offset and some vlookups ... so they should be just fine i think.


Regards,

Alvino

 

[Alvino]

Hi Hui,


For your info also:


I changed the Pivot tables to refresh on opening the file. So far, it seesm to solve the 5s delay. However, it means that i have to look at how i store the data file in the shared drive ...


Thanks!


Regards,

Alvino