Tuesday, 8 March 2016

How to Refresh a Pivot Cache and Clear Missing Pivot Items in VBA

Excel Macro to Refresh a Pivot Cache and Clear Missing PivotItems
Sub PvtCache_Clear()

Dim My_Pvt As PivotTable

Set My_Pvt = ThisWorkbook.Sheets("My_Tab").PivotTables("MyPivot")

Clearing Missing Items from Pivot Fields and Refreshing Pivot Cache
My_Pvt.PivotCache.MissingItemsLimit = xlMissingItemsNone
My_Pvt.PivotCache.Refresh

'My_Pvt.ManualUpdate = False

'Refreshing the Pivot Table
My_Pvt.RefreshTable

X = My_Pvt.PivotFields("MyField").PivotItems.Count

MsgBox X

'Displaying Pivot Items after Pivot Refresh
For Y = 1 To X

   MsgBox My_Pvt.PivotFields("MyField").PivotItems(Y)

Next Y

End Sub

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts