title: change pivot source using vba won't work
body: <p>I have been puzzling to have vba change the datasource for multiple pivots.
  I have been reading many suggestions and none of them have helped me so far. I do not understand what I am doing wrong so I ultimately ask my question here.</p>
  
  <p>Here is what I try to achieve:
  I have multiple files with pivots that have to be changed because columns have been inserted and my pivot data source has changed. I want to change this to the right range. I created this code:</p>
  
  <pre><code>Sub change_pivotsource()
  Dim pt As PivotTable
  Dim pts As PivotTables
  Dim pt_source As String
  Dim ws As Worksheet
  Dim wb As Workbook
  
  Set wb = ActiveWorkbook
  
  workbookname = ActiveWorkbook.Name
  sheetname = Left(workbookname, Len(workbookname) - 5)
  pt_source = "'" &amp; sheetname &amp; "'!$A:$T"
  
  
  For Each ws In wb.Worksheets
      For Each PivotTable In ws.PivotTables
        pt.SourceData = pt_source
      Next PivotTable
  Next ws
  
  
  End Sub
  </code></pre>
  
  <p>I am using the workbook name to have the string which is the final datasource location.</p>
  
  <p>How can I have the pt_source as my pivotsource for each pivot in my workbook?
  Many times I get an "object or block variable with is not set" error</p>
  
  <p>edit 1:</p>
  
  <p>this code give me an error 7 during execution: insufficient memory:
  and then it highlights the "pt_source ="-row.</p>
  
  <pre><code>Sub change_pivotsource()
  Dim ws As Worksheet
  Dim wb As Workbook
  Dim pt As PivotTable
  Dim sheetname As String
  Dim pt_source As String
  Dim workbookname As String
  
  workbookname = ThisWorkbook.Name
  sheetname = Left(workbookname, Len(workbookname) - 5)
  pt_source = Sheets(sheetname).Range("$A:$T")
  
  For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
          pt.ChangePivotCache _
          wb.PivotCaches.Create(SourceType:=xlDatabase, _
                SourceData:=pt_source)
  
        Next pt
  Next ws
  'workbook.save
  End sub
  </code></pre>
  
  <p>this code solved my question:</p>
  
  <pre><code>Sub UpdatePivotCaches()
  ' Updates each pivot cache in the current workbook.
  Dim pt As PivotTable        ' Used to loop over each pivot table.
  Dim ws As Worksheet         ' Used to loop over each work sheet.
  Dim sheetname As String
  Dim pt_source As String
  Dim workbookname As String
  
  workbookname = ThisWorkbook.Name
  sheetname = Left(workbookname, Len(workbookname) - 5)
  
  
      ' Loop over each sheet.
      For Each ws In ActiveWorkbook.Sheets
  
          ' Loop over each pivot table on the sheet.
          For Each pt In ws.PivotTables
  
              ' Update the pivot cache.
              pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, "'" &amp; sheetname &amp; "'!$A:$T", xlPivotTableVersion14)
          Next
      Next
  End Sub
  </code></pre>
  
  <p>Key to the solution might have been the version parameter (see the comments below this question).
  Thanks to destination-data</p>