title: Share PivotCache for PivotTables built with data model
body: <p>I am just cleaning up my workbook and I have used the following code to consolidate my PivotCaches (I had around 200 prior to the cleaning).</p>
  
  <pre><code>Sub changeCache()
  Dim ws As Worksheet
  Dim pt As PivotTable
  Dim pc As PivotCache
  Dim first As Boolean
  On Error Resume Next
  
      For Each ws In ActiveWorkbook.Worksheets
          ws.Activate
          For Each pt In ActiveSheet.PivotTables
  
              If first = False Then
                  Set pc = pt.PivotCache
                  first = True
              End If 
  
              pt.CacheIndex = pc.Index
  
          Next pt
      Next ws
  
  End Sub
  </code></pre>
  
  <p>This has reduced my PivotCache count to 33.</p>
  
  <pre><code>Sub CountCaches()
    MsgBox ActiveWorkbook.PivotCaches.Count
  End Sub
  </code></pre>
  
  <p>The reason it is 33 and not 1 is because I have 32 PivotTables that are built with the <strong>Data Model</strong>.</p>
  
  <p><strong>My question is:</strong> Does anyone know how to change PivotTables built with the Data Model to all use the same PivotCache?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p><strong>My secondary question is:</strong> Do multiple pivot tables all built on a data model </p>
  
  <p>a) reference a single data model; or </p>
  
  <p>b) each have their own model and therefore 'bloat' the Excel file</p>
  
  <p><strong>EDIT2</strong></p>
  
  <p>On further exploration, it appears that the data model <strong>is</strong> shared for pivot tables that reference the same data. This can be seen in 'Connections' (found under the 'Data' tab in the ribbon). In theory, this shouldn't 'bloat' the file even though the code <code>ActiveWorkbook.PivotCaches.Count</code> counts each pivot table that shares a connection and falsely(?) indicates multiple caches. </p>
  
  <p>I will however leave the bounty open in case someone can provide a more definitive answer.  </p>