Refreshing single PivotTable only

vba pivot-table partial-page-refresh
VeryBasicAnswers picture VeryBasicAnswers · Jan 15, 2016 · Viewed 8.7k times · Source

In Excel 2013 I have two pivot tables named "A" and "B". I need to refresh only one pivot table ("B") on sheet of my workbook for comparison.

I tried all below and it looks as if it should work as required - yet none of them will (i.e. both pivots will refresh after running any of the solutions). I tried changing Pivot Table Data options to all possible combinations with no change to outcome.

Sub refresh_pivot1() 

'=================================================
Windows("File.xlsx").Activate
Sheets("Pivots").Select
'=================================================
ActiveSheet.PivotTables("B").PivotCache.Refresh
'=================================================
Worksheets("Pivots").PivotTables("B").RefreshTable
'==================================================

Dim PvtTbl As PivotTable            

For Each PvtTbl In Worksheets("Pivots").PivotTables            

    If PvtTbl = "B" Then                                            
        PvtTbl.RefreshTable          
    End If             

Next

'=================================================          
Range("G3:K12").Calculate             
'=================================================           
Dim pt As PivotTable             
Set pt = ActiveSheet.PivotTables("B")           
pt.RefreshTable            
'=================================================

End Sub

Answer

jeffreyweir picture jeffreyweir · Jan 15, 2016

If both PivotTables are based on the same data source, then they share what's called a PivotCache. When you refresh a PivotTable, you are in fact refreshing the underlying PivotCache, and not an individual PivotTable. This means that when you refresh one PivotTable, you are actually refeshing *all' PivotTables that share that cache.

You need to tell Excel to assign your second PivotTable to a different Cache. See http://www.contextures.com/xlPivot11.html for code on how to do this (particularly the section "Create New Pivot Cache for Selected Pivot Table") or give Google a spin.

Related questions

Filter Excel pivot table using VBA

I have tried copying and pasting solutions from the internet forever now to try to filter a pivot table in Excel using VBA. The code below doesn't work. Sub FilterPivotTable() Application.ScreenUpdating = False ActiveSheet.PivotTables("PivotTable2").ManualUpdate = True ActiveSheet.PivotTables("…

Read More
refresh both the External data source and pivot tables together within a time schedule

In my last post Auto refresh pivottables data in excel on first run, i found that on my first execution the query from the External data source is refreshed and takes approximately 1 min to execute. and in my second run, …

Read More
How can I use VBA to add a Connection (to an External Data Source) in Excel and Save it to that Excel spreadsheet's list of Connections

I can use VBA to create a new ADODB.Connection and associated ADODB.Command and ADOBD.Parameter and then create a PivotCache and a PivotTable Sub CreatePivotTable() 'Declare variables Dim objMyConn As ADODB.Connection Dim objMyCmd As ADODB.Command Dim …

Read More