CODEWITHSUNDEEP
excelvbapivot-table
urdearboy
urdearboy

Reputation: 14580

Refresh One Pivot with Shared Cache

I'm having issues with a macro that should allow me to refresh pivot tables and avoid the overlapping error message. I thought I found a sneaky way to give the appearance of this happening but am running into a issue with shared data source.

I have 5 pivot tables (each with the same data source: Table1) in the below structure

Column A
----------
Pivot 1
'Two Blank Rows
Pivot 2
'Two Blank Rows
Pivot 3 
'Two Blank Rows
Pivot 4
'Two Blank Rows
Pivot 5
'Two Blank ROws

The macro aims to move Pivot 1 over 6 columns, refresh that pivot alone (allowing it to expand or compress as much as needed). Then the macro moves Pivot 2 over 6 columns and situates it two rows below the first pivot, refresh that pivot alone, etc. The process repeats for all pivots and then I delete the 6 columns giving the impression that all tables are back in their original starting point except the table rows have expanded/compressed accordingly without running into the issue of ERROR: A pivot table cannot overlap another pivot table. When ScreenUpdating is toggled off, this gives the impression that pivots are refreshing and dynamically adjusting their position to allow for expansion/compression of the surrounding tables.

The issue I am having is that when I refresh one pivot, the other 4 automatically try to refresh which produces the overlap error for the other 4 tables. How can I only refresh one pivot table at a time despite a common Pivot Cache?

I have tried the two lines of code in center of loop and I have tried to use a macro found on this link that aims to break apart the shared pivot cache allowing for independent refreshes. The macro on this link just crashes my instance of excel each time. 

For i = LBound(OTCPvts) To UBound(OTCPvts)
    LRow = OTC.Range("O" & OTC.Rows.Count).End(xlUp).Offset(3).Row
    OTC.PivotTables(OTCPvts(i)).TableRange2.Cut OTC.Range("M" & LRow)
    On Error Resume Next

        OTC.PivotTables(OTCPvts(i)).PivotCache.Refresh 'Gives Overlap Error
        OTC.PivotTables(OTCPvts(i)).RefreshTable 'Does not refresh/change table

    On Error GoTo 0
Next i

Showing relevant code only - the process goes on to filter/sort before moving on to Next i but this all works fine

Upvotes: 2

Views: 877

Answers (1)

Tate Garringer
Tate Garringer

Reputation: 1529

So, upon revisiting this, I discovered another kind of hacky workaround that doesn't involve moving the tables more than once. While refreshing a shared pivot cache seems to update all of the pivot tables sharing the cache, you could apply an xlTopCount filter with a value of 1 to all of them before refreshing, then move each table and remove the filter

For i = LBound(OTCPvts) To UBound(OTCPvts)
    With OTC.PivotTables(OTCPvts(i))
        .ClearAllFilters
        .PivotFields("FieldName").PivotFilters.Add2 _
            Type:=xlTopCount, DataField:=.PivotFields("OtherFieldName"), Value1:=1
        .PivotCache.Refresh
        .RefreshTable
    End With
Next i
For i = LBound(OTCPvts) To UBound(OTCPvts)
    With OTC.PivotTables(OTCPvts(i))
        lRow = OTC.Range("O" & OTC.Rows.Count).End(xlUp).Offset(3).Row
        .TableRange2.Cut OTC.Range("M" & lRow)
        .ClearAllFilters
    End With
Next i

Turns out neither changing/refreshing the pivot cache or refreshing the table removes a filter, so this is a good way to prevent overlapping by forcing your table's size until it's been moved and the filter removed.

Upvotes: 1

Related Questions