CODEWITHSUNDEEP
vbaexcelexcel-2010
Kai
Kai

Reputation: 2158

Invalid Procedure Call Or Argument - attempting to dynamically change pivot cache

I'm trying to dynamically refresh some pivot tables in Excel. The pivot tables (and connected slicers - each slicer connects to multiple pivots) already exist, the underlying source data table does not. The process is as follows:

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

I'm running into a problem however 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, it means one pivot cache per pivot table, which means I run into problems when trying to set up slicers that manipulate multiple pivots - it assumes that each pivot table has a different data source, and so will only let me link the slicer to a single pivot.

I decided the way to go would be to create a single pivot cache, and then link each pivot table to it. This code however does not work, throwing error 5 at me the first time it is reached:

'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?

Upvotes: 5

Views: 4041

Answers (2)

Robert Goodyear
Robert Goodyear

Reputation: 1

If you hate snorkeling the code, to make it 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

Upvotes: 0

Rory
Rory

Reputation: 34045

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

Upvotes: 2

Related Questions