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

Dim J as Integer

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

'De-Selecting the (blanks) from the Pivot
J = My_Pvt.PivotFields("MyField").PivotItems.Count

If J > 2 Then
    With My_Pvt.PivotFields("MyField")
        .PivotItems("(blank)").Visible = False
    End With

Else: End If

End Sub


No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts