title: Using variables in pivot data source
body: <p>I would like to replace:</p>
  
  <p><code>C:\Users\test_h\Downloads\New folder\[Book1.xlsm]</code> and <code>Sheet1</code> with a variable from the code below:  </p>
  
  <pre><code>ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
  
   PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
  
   "C:\Users\test_h\Downloads\New folder\[Book1.xlsm]Sheet1!R1C1:R30C14", Version _
   :=xlPivotTableVersion14)
  </code></pre>
  
  <hr>
  
  <p>I've tried, but cannot get it to work:</p>
  
  <pre><code>Sub test(sht As String, path_name As String)
  
     ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
  
     PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
  
     path_name &amp; sht &amp; !R1C1:R30C14", Version _
  
     :=xlPivotTableVersion14)
  
  End Sub
  </code></pre>