title: Excel 2007 VBA - Pivot table field list??? creates error
body: <hr>
  
  <p>Last update on October 14, 2011 - I know more info now, so I will summarize everything here and delete all my previous descriptions/questions.</p>
  
  <p>The problem is, after running a certain program, I get some weird error.  It's similar to, but not exactly like, when I click "Split".  The difference is the solid gray bars do not show up.  It's sort of weird.  More recently, I have started thinking it is probably caused by the field list of the pivot tables involved.</p>
  
  <p>The problem occurs with any of the three following pieces of code</p>
  
  <p>Code 1</p>
  
  <pre><code>Dim pvtCache As PivotCache
  For Each pvtCache In ThisWorkbook.PivotCaches
      pvtCache.Refresh
  Next pvtCache
  </code></pre>
  
  <p>Code 2</p>
  
  <pre><code>ThisWorkbook.RefreshAll
  </code></pre>
  
  <p>Code 3</p>
  
  <pre><code>Dim ws As Worksheet
  Dim pt As PivotTable
  For Each ws In ThisWorkbook.Worksheets
      For Each pt In ws.PivotTables
          pt.RefreshTable
      Next pt
  Next ws
  </code></pre>
  
  <p>But not with this, where the only difference is I added ws.Activate to Code 3</p>
  
  <p>Code 4</p>
  
  <pre><code>Dim ws As Worksheet
  Dim pt As PivotTable
  For Each ws In ThisWorkbook.Worksheets
      ws.Activate
      For Each pt In ws.PivotTables
          pt.RefreshTable
      Next pt
  Next ws
  </code></pre>
  
  <p>So, my question is, can any one explain this?  Can any one fix Code 1 (which is much faster in a spreadsheet with 2 pivot caches from large data sources, but 46 pivot tables) to make it work without this error?</p>
  
  <p>Thanks for any help!!!</p>