Clever Geek Handbook

Invalid procedure call or argument - Attempt to dynamically change the pivot cache

I am trying to dynamically update some pivot tables in Excel. Pivot tables (and connected slicers - each slicer connects to multiple centers) already exist, there is no source data in the table. The process looks like this:

  • Create a new sheet for storing "raw" data
  • Fill sheet, wrap data in ListObject (table)
  • Create a new pivot cache from new data
  • Detach slicers from pivot tables
  • Change your pivot table cache to a new cache
  • Refresh pivot tables
  • Link slicers backups
  • Delete data sheet

To clarify the structure: One data source table. Several pivot tables pointing to the source. Multiple slicers, each connected to all pivot tables (e.g. Week Ending slicer chages Week Ending on all corners)

I ran into a problem, but with step 4. The following code works:

'dataTable is a ListObject that was created on a sheet earlier in the function. Can confirm 100% that it exists and is populated.    
Dim pt As PivotTable
For Each pt in PivotSheet.PivotTables
    pt.ChangePivotCache ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=dataTable)
Next pt

However, this means there is one pivot cache per pivot table, which means that I am running into problems when trying to set up slicers that manage multiple anchor points - each pivot table is assumed to have a different data source and so I will let me link slicer to a single stem.

I figured I would need to create a single pivot cache and then bind each pivot table to it. This code, however, doesn't work, throwing error 5

at me the first time:

'dataTable is a ListObject that was created on a sheet earlier in the function. Can confirm 100% that it exists and is populated.
Dim pc As PivotCache
Set pc = ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=dataTable)

Dim pt As PivotTable
For Each pt in PivotSheet.PivotTables
    pt.ChangePivotCache pc  'Invalid Procedure Call Or Argument 
Next pt

What am I doing wrong here?

+3


source to share


2 answers


Combine the two approaches:



Dim bCreated              As Boolean
Dim lngMasterIndex        As Long
Dim pt                    As PivotTable

For Each pt In PivotSheet.PivotTables
    If Not bCreated Then

        pt.ChangePivotCache ThisWorkbook.PivotCaches.Create( _
                            SourceType:=xlDatabase, _
                            SourceData:=DataTable)
        bCreated = True
        lngMasterIndex = pt.CacheIndex
    Else
        pt.CacheIndex = lngMasterIndex
    End If
Next pt
+1


source


If you hate scuba diving in code to get it to work, this is the short and dirty way to do it:



For Each pt in PivotSheet.PivotTables
  On Error Resume Next
    pt.ChangePivotCache pc 'works for the first entry
    pt.CacheIndex = pc.Index 'works for all the others
  On Error GoTo 0
Next PT
0


source






More articles:


All Articles