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.ManualUpdate = False

'Refreshing the Pivot Table
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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts