Pivot Table Cache clear

[DRANDON]

I have a code snipit to clear my PT Cache prior to entering a new data set.

Sub Clean_Pivots()
For Each pt In ActiveSheet.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next
End Sub

Will any harm come to me if I have this code in upon worksheet.activate event rather than maybe having a button to execute the code only when I need to?

Thanks.

 

[Fazza]

I have a code snipit to clear my PT Cache prior to entering a new data set.

Sub Clean_Pivots()
For Each pt In ActiveSheet.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next
End Sub

Will any harm come to me if I have this code in upon worksheet.activate event rather than maybe having a button to execute the code only when I need to?

No. (You might want an extra step between 'pt.pivotcache.etc' & 'next': pt.pivotcache.refresh)

 

[DRANDON]

Thank you. I elected to put all the refresh code in a module and invoke it with a control button. Looks like this
Sheets("TOP 10 IP OP GRAPH").Activate
ActiveSheet.Unprotect
ActiveSheet.PivotTables("pivottable1").PivotCache.MissingItemsLimit = xlMissingItemsNone
ActiveSheet.PivotCache("pivottable1").Refresh
ActiveSheet.PivotTables("pivottable1").RefreshTable
I have 15 sheets with about 20 pivit tables altogether.
Thanks for your reply.

 

[Fazza]

OK. To update all in the one workbook, see code below. Note it is best to not activate objects unless necessary.

Also note I have ignored any need to unprotect/protect sheets. If you have sheet protection, please adjust to suit.

Sub All()

  Dim wks As Worksheet
  Dim pt As PivotTable

  For Each wks In Worksheets
    For Each pt In wks.PivotTables
      With pt.PivotCache
        .MissingItemsLimit = xlMissingItemsNone
        .Refresh
      End With
    Next pt
  Next wks

  Set pt = Nothing
  Set wks = Nothing

End Sub