Error 1004 when assigning a new value to a Pivot Table .CacheIndex

[Laget]

Hello, everyone!

This is my first post here, because it's the first time I did not find a satisfactory answer to an issue...

The problem is, I have a file with 21 sheets, each one has 3 or 4 pivot tables; all pivot tables INSIDE A SHEET connect to a specific data source (which is a range in another workbook). Each sheet has also a lot of slicers, which I previously disconnect before updating data source, and then reconnect. All the process was manual (we update all PTs once per month), but now I came up with a code to automatically get the new range (in another open workbook) and assign it to all Pivot Tables inside a specific sheet.

I first applied the code to a test workbook, much simpler, which worked fine. But now I'm getting an error (1004: Application-defined or Object-defined error) at a point. I'll just post the code below:

Sub AtualizaFonteTabDin()

Dim PT                          As PivotTable
Dim ptMain                      As PivotTable
Dim ws                          As Worksheet
Dim lIndex                      As Long
Dim NomeDoArquivo               As String

'get the file name of the workbook containing the data source
For Each Workbook In Application.Workbooks
    If left(Workbook.Name, 8) <> "Tracking" Then
    
      NomeDoArquivo = Workbook.Name
    End If

Next Workbook
                                        
   ' update pivottables
For Each PT In ActiveSheet.PivotTables

         If lIndex = 0 Then
 
            PT.ChangePivotCache _
                  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                             SourceData:="[" & NomeDoArquivo & "]" & Workbooks(NomeDoArquivo).Sheets("Perfil_01-Tableau").Name & "!" & Workbooks(NomeDoArquivo).Sheets("Perfil_01-Tableau").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1))
                                                                    
            Set ptMain = PT

            lIndex = 1

         Else

[U][B]            PT.CacheIndex = ptMain.CacheIndex [/B][/U]     '<---- That's when the error hits, in the second loop of the For statement 

         End If

      Next PT

End Sub

Specifically, ptMain.CacheIndex (the new cacheIndex just assigned to PivotTable(1) has a value of 22, while the other PivotTables have a .CacheIndex = 16, and the error happens when the macro tries to assign the 22 value to them.

Note: I input the Source sheet manually, because it varies from file to file - but that is not a problem.

I'm working all day on this issue, and would be extremely grateful for any insights!

 

[steve the fish]

Havent looked extensively but what is ptMain? Doesnt look like the macro knows which pivot this is.

 

[Laget]

Havent looked extensively but what is ptMain? Doesnt look like the macro knows which pivot this is.

ptMain is declared as a PivotTable; basically, the program iterates for all pivot tables in a worksheet and the first one, which receives the new data source, is declared as ptMain:

Set ptMain = PT

While the subsequent Pivot Tables are supposed to be assigned with ptMain's cache in the second and following iterations:

PT.CacheIndex = ptMain.CacheIndex

But that's when the code fails...

 

[steve the fish]

I understand its a pivot table. But nowhere in your code does it say which pivot table.Try explicitly declaring which pivot is ptMain. ie its sheet and name. See how the code runs then.

 

[Laget]

Thanks for your interest, Steve!

The ptMain is set just fine, I checked its properties and it's what I wanted it to be.

I'm now suspecting that the problem are the Pivot Tables' fields; an explanation of the .CacheIndex property in MSDN:

If you set the CacheIndex property so that one PivotTable report uses the cache for a second PivotTable report, the first report’s fields must be a valid subset of the fields in the second report.

The problem is, I have 4 distinct pivot tables per sheet, each one has distinct fields, even though they all connect to the same source range...

Perhaps this is causing the error? Can it be done somehow?

 

[steve the fish]

This type of thing works for me:

Sub Macro1()
Dim pt As PivotTable
Dim ptMain As PivotTable
Set ptMain = Sheets("Sheet3").PivotTables("PivotTable4")

For Each pt In ActiveSheet.PivotTables
pt.CacheIndex = ptMain.CacheIndex
Next
End Sub