title: Dynamic SourceData in pivot create
body: <p>I recorded a macro of creating a pivot table.</p>
  <pre><code>ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=&quot;Sheet1!R1C1:R30098C15&quot;, Version:=6).CreatePivotTable TableDestination:=&quot;Resultat!R3C1&quot;, TableName:=&quot;Pivottabell1&quot;, DefaultVersion:=6
  </code></pre>
  <p>Now I want the SourceData to be dynamic with the size of the table.<br />
  I have unsuccessfully tried the following</p>
  <pre><code>' get the rows count and column counts in the R1C1 style
  SourceData:=&quot;Sheet1!R1C1:R&quot; &amp; Sheets(&quot;Sheet1&quot;).Cells(Sheets(&quot;Sheet1&quot;).Rows.Count, &quot;A&quot;).End(xlUp).Row &amp; &quot;C&quot; &amp; Cells(1, 1).End(xlToRight).Column
  
  ' using a &quot;normal&quot; style range with a column far outside the range used
  SourceData:=&quot;Sheet1!A1:CC&quot; &amp; Sheets(&quot;Sheet1&quot;).Cells(Sheets(&quot;Sheet1&quot;).Rows.Count, &quot;A&quot;).End(xlUp).Row
  
  ' using currentregion
  SourceData:=&quot;Sheet1!&quot; &amp; Range(&quot;A1&quot;).CurrentRegion.Address
  
  ' using currentregion without &quot;$&quot;
  SourceData:=&quot;Sheet1!&quot; &amp; Replace(Range(&quot;A1&quot;).CurrentRegion.Address, &quot;$&quot;, &quot;&quot;)
  </code></pre>
  <p>What is wrong with these syntaxes?</p>