title: Change dynamic source data for pivot table Excel Macro
body: <p>I've created a macro that auto updates/changes the source data of my pivot.</p>
  
  <p>The problem with my macro is the source data is not static. The number of rows in trialbalance sheet changes daily so the cell reference <code>A1:F500</code> is not applicable to what I'm trying to achieve.</p>
  
  <p>The number of column is fixed always (columns A to F).</p>
  
  <p>If someone could lend his or her helping hand on how can I detect how to get the top up to the bottom of the report found in the trialbalance sheet? That would be great!</p>
  
  <pre><code>Sheets("mtd move").pivottables("pvtmtd").SourceData = Sheets("trialbalance").[A1:F500].CurrentRegion.Address(True, True, xlR1C1, True)
  ActiveWorkbook.ShowPivotTableFieldList = False
  </code></pre>