title: Error when creating VBA for changing data source in several Pivot Tables
body: <p>I am trying to create a VBA to change the data source in several Pivot tables in the same datafile and worksheet.
  The data source is in another workbook and is updated weekly with new data.</p>
  
  <p>"FY 18.xlsb" - Workbook with data</p>
  
  <p>"Data" - Worksheet in "FY 18.xlsb" containing whe data</p>
  
  <p>"Pivot Table" - The Worksheet in the active Workbook where Pivot Tables that should be updated are found.</p>
  
  <p>Either I want the VBA to find the end point in the data-worksheet or to have a pop-up window where I can choose the datarange.</p>
  
  <p>Get stopped when running Debug on the code at "pt.ChangePivotCache pc"</p>
  
  <p>Anyone having any ideas what can be wrong? Or any ideas how the code can be updated?</p>
  
  <pre><code>Sub AdjustPivotDataRange()
  Dim pt As PivotTable, pc As PivotCache
  Dim dataSheet As Worksheet, ws As Worksheet, wks As Worksheet
  Dim StartPoint As Range, dataSource As Range, NewRange As String
  
  ' Datasource workbook and worksheet
  Set wkb = Workbooks("FY 18.xlsb")
  Set wks = wkb.Worksheets("Data")
  
  ' Worksheet
  Set ws = ActiveWorkbook.Worksheets("Pivot Table")
  
  ' Dynamically retrieve range address of data
  Set StartPoint = wks.Range("A1")
  Set dataSource = wks.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
  NewRange = wks.Name &amp; "!" &amp; dataSource.Address(ReferenceStyle:=xlR1C1)
  
  ' Create new PivotCache
  Set pc = ThisWorkbook.PivotCaches.Create( _
             SourceType:=xlDatabase, _
             SourceData:=NewRange)
  
  ' Loop through all tables in worksheet
  For Each pt In ws.PivotTables
  
          ' update pivot source and refresh
          pt.ChangePivotCache pc
          pt.RefreshTable
  
  Next pt 
  End Sub
  </code></pre>
  
  <p>EDIT:
  New code that does what I want!</p>
  
  <pre><code>Sub AdjustPivotDataRange()
  Dim pt As PivotTable, pc As PivotCache
  Dim dataSheet As Worksheet, ws As Worksheet, wks As Worksheet
  Dim StartPoint As Range, dataSource As Range, NewRange As String
  
  ' Datasource workbook and worksheet
  Set wkb = Workbooks("Nordics SQI - FY 19.xlsb")
  Set wks = wkb.Worksheets("Data")
  
  ' Worksheet
  Set ws = ActiveWorkbook.Worksheets("Pivot Table")
  
  ' Dynamically retrieve range address of data
   Set dataSource = wks.Range("A1").CurrentRegion
  
  ' Loop through all tables in worksheet
  For Each pt In ws.PivotTables
  
          ' update pivot source and refresh
          pt.ChangePivotCache ThisWorkbook.PivotCaches.Create( _
             SourceType:=xlDatabase, _
             SourceData:=dataSource)
          pt.RefreshTable
  
  Next pt
  
  End Sub
  </code></pre>