title: Change Pivot table data source using VBA
body: <p>I am trying to automatically try to change the "Data Source" of a pivot table in excel. I have tried most if not all of the solutions available on Google/Stack Overflow. However I keep encountering the following error.</p>
  
  <pre><code>Run-time error '5':
  Invalid procedure call or argument
  </code></pre>
  
  <p>Here is the code that I'm running that fails.</p>
  
  <pre><code>Sub UpdateResourcePivots()
  Dim pivotDataSheet As Worksheet
  Dim dataSheet As Worksheet
  Dim pivotLocation As String
  Dim pivotDesignation As String
  Dim startPoint As Range
  Dim endPoint As Range
  Dim dataRange As Range
  Dim newRange As String
  Dim lastRow As Integer
  
  Dim newPivotCache As PivotCache
  
  Set pivotDataSheet = ThisWorkbook.Worksheets("PivotData")
  Set dataSheet = ThisWorkbook.Worksheets("Team Information")
  
  pivotLocation = "pvtResourceLocation"
  pivotDesignation = "pvtResourceDesignation" 'Dont worry about this one for now
  
  Set startPoint = dataSheet.Range("A5")
  lastRow = dataSheet.Range("A" &amp; dataSheet.Rows.count).End(xlUp).row
  Set endPoint = dataSheet.Range("O" &amp; lastRow)
  Set dataRange = dataSheet.Range(startPoint, endPoint)
  
  newRange = "'" &amp; dataSheet.name &amp; "'!" &amp; dataRange.Address(RowAbsolute:=True, ColumnAbsolute:=True, ReferenceStyle:=xlR1C1, External:=True)
  ' newRange = "'" &amp; dataSheet.name &amp; "'!" &amp; dataRange.Address(ReferenceStyle:=xlR1C1) ' Tried this as well
  
  Set newPivotCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=newRange, Version:=xlPivotTableVersion10)
  
  With pivotDataSheet
      .PivotTables(pivotLocation).ChangePivotCache newPivotCache 'Error thrown here.
  
  End With
  End Sub
  </code></pre>
  
  <p>The error is thrown at the following line:</p>
  
  <pre><code>.PivotTables(pivotLocation).ChangePivotCache newPivotCache
  </code></pre>
  
  <p>Some other info</p>
  
  <blockquote>
    <p>I am using MS Office 2010 (licensed).
    I have one source of data (a small table with 20 rows and 8 columns; the rows will grow hence the macro). Created two Pivot Tables on another sheet. On a third sheet I'm using Slicers to filter the data (Dashboard). </p>
  </blockquote>