Subscribe
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
