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

Laget

New Member
Joined
Jan 20, 2015
Messages
3
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:

Code:
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!
 
Last edited:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,778
Office Version
365
Platform
Windows
Havent looked extensively but what is ptMain? Doesnt look like the macro knows which pivot this is.
 

Laget

New Member
Joined
Jan 20, 2015
Messages
3
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:

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

Code:
PT.CacheIndex = ptMain.CacheIndex
But that's when the code fails...
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,778
Office Version
365
Platform
Windows
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

New Member
Joined
Jan 20, 2015
Messages
3
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,778
Office Version
365
Platform
Windows
This type of thing works for me:

Code:
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
 

Forum statistics

Threads
1,081,793
Messages
5,361,325
Members
400,627
Latest member
Mcomeaux

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top