title: VBA clear just pivot table cache, but leaving pivot table structure
body: <p>How can I clear pivot table cache with VBA, but not destroy pivot table structure? My pivot table is connected to external data source. The SQL source determines which user should see which portion of the data. The source populates the table as the table is refreshed. I want to save the Excel file and distribute it with clean pivot table (no data inside). </p>
  
  <p>As a result I want to get exactly this:</p>
  
  <p><a href="https://i.stack.imgur.com/S0cHe.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/S0cHe.png" alt="enter image description here"></a></p>
  
  <p>I have experimented around this code with no success. There is no such thing like PivotCaches.Clear in VBA.</p>
  
  <pre><code>Sub PT_cache_clear()
      For Each pc In ActiveWorkbook.PivotCaches
  
          pc.Clear
  
      Next pc
  End Sub
  </code></pre>
  
  <p>The only good solution I found is to refresh the table with a user which has access to SQL server source but is not allowed to see any single record of the data.</p>
  
  <p>The idea:</p>
  
  <pre><code>ActiveSheet.PivotTables("PivotTable1").SaveData = False
  </code></pre>
  
  <p>seems not to lead to desired results. </p>