Tuesday, March 8, 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

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog