CODEWITHSUNDEEP
vbaexcelpivot-table
Sean Bailey
Sean Bailey

Reputation: 375

Pivot Cache Refresh VBA

Hi I have a pivot table set up which I need to refresh on a VBA command. However when I refresh the pivot command it excludes some of the required Column label Values. I believe I have to change the Pivot Cache which was set up originally but not sure how to? (Can anyone advise how to do this?)

The code I am using is below: 

Worksheets("Summary by Account").PivotTables("PivotTable1").RefreshTable

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
For Each pt In ActiveSheet.PivotTables
    pt.ManualUpdate = True
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    For Each pf In pt.PivotFields
        If pf.Orientation <> 0 Then
            If pf.Orientation = xlPageField Then
                pf.CurrentPage = "(All)"
            Else
                For Each pi In pf.PivotItems
                    pi.Visible = True
                Next pi
            End If
        End If
    Next pf
    pt.ManualUpdate = False
Next pt
Set pi = Nothing
Set pf = Nothing
Set pt = Nothing
Set wks = Nothing

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Nominal / Category")
    .PivotItems("(blank)").Visible = False
End With

Upvotes: 1

Views: 3751

Answers (3)

ChrisB
ChrisB

Reputation: 3205

I use this code to reset all pivot table caches in the entire workbook and prevent the pivot table drop-down filters from showing missing items that don't actually exist in the underlying data.

Sub PivotCacheReset()
' When a data set is pivoted and then some of the underlying data is
'  removed, even after a refresh old removed values can still remain in the
'  pivot filter.  This macro changes the properties of all pivot tables in
'  the active workbook, to prevent missing items from appearing, or clear
'  items that have appeared. It also resets all pivot table caches in the
'  active workbook.

    Dim wb As Workbook
    Set wb = ActiveWorkbook

    Dim iWs As Worksheet
    Dim pvt As PivotTable
    Dim iProtectState As Boolean

    ' Loop through each worksheet.
    For Each iWs In wb.Worksheets

        ' If the worksheet is protected, unprotect it. Remember
        '  it was protected so that it can be re-protected later.
        iProtectState = False                    ' Reset variable that remembers if each sheet is protected.
        If iWs.ProtectContents = True Then
            ' Worksheet is protected.
            iWs.Unprotect                        ' Unprotect the worksheet.
            iProtectState = True                 ' Remember that this worksheet was protected.
        End If

        ' Loop through each pivot table in the sheet.
        For Each pvt In iWs.PivotTables
            pvt.PivotCache.MissingItemsLimit = xlMissingItemsNone ' Don't allow missing items in the pivot table filters.
            pvt.PivotCache.Refresh               ' Reset the pivot table cache including any missing items.
        Next pvt

        ' If the worksheet was originally protected, re-protect it.
        If iProtectState = True Then iWs.Protect

    Next iWs

End Sub

Upvotes: 0

Tabias
Tabias

Reputation: 100

My method to updating pivot caches with new information is to store all of the data on a separate sheet within the workbook and put in an extending range formula named range - with this formula

=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

Then, in VBA, use:

ThisWorkbook.RefreshAll

Hope that helps!

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33672

I am using the following code to refresh my PIVOT Table Cache, my source file is located in a remote workbook, so you can adjust the parameters to your needs.

Const Pivot_sht_str             As String = "Summary by Account"
Const Pivot_name_str            As String = "PivotTable1"
Dim pt                          As PivotTable

Dim Data_FilePath               As String
Dim Data_book                   As String
Dim Data_sht_str                As String
Dim Data_sht                    As Worksheet
Dim Data_range                  As Range
Dim Data_range_str              As String
Dim lrow                        As Long

' Asign Pivot table to pt
Set pt = Worksheets(Pivot_sht_str).PivotTables(Pivot_name_str)

' Asign Pivot's Data source parameters >> this part is needed only if source for PIVOT table is data in another workbook
Data_FilePath = "\\Folder\Nested Folder\Nested Folder 2\" 'modify to your needs
Data_book = "File_Name.xlsx" 'modify to your needs
Data_sht_str = "Worksheet Name" 'modify to your needs

Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Workbooks.Open (Data_FilePath & Data_book)

Set Data_sht = ActiveWorkbook.Worksheets(Data_sht_str)
lrow = Data_sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Data_range_str = Data_sht.Name & "!" & Range("$A$1:$P$" & lrow).Address '.Address(ReferenceStyle:=xlR1C1)  ' adjust Columns to your needs
Set Data_range = Data_sht.Range(Data_range_str)

' check if Data Range consists of a legal range, Workbook and worksheet are legal
If Not Data_range Is Nothing Then
    Data_range_str = Data_FilePath & "[" & Data_book & "]" & Data_sht.Name & "!" & Range("$A$1:$P$" & lrow).Address

    'Change Pivot Table Data Source Range Address (refresh it's cache data)
    pt.ChangePivotCache ThisWorkbook.PivotCaches.Create(xlDatabase, Data_range_str, xlPivotTableVersion12) ' verify last parameter to have the correct Excel version
    pt.RefreshTable
    On Error Resume Next

    ActiveWorkbook.Close (False)  ' Workbooks(Data_FilePath & Data_book).Close savechanges:=False
End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True

If Data_range Is Nothing Then Exit Sub

Upvotes: 1

Related Questions