title: Get Source Range of a PivotTable
body: <p>I am building a set of reports to be updated daily for some users who are not super savvy when it comes to pivot charts and pivot tables in Excel (2010). I have data from an already automated data pull from our db, but to make that information useful (and more simple to use), I want to generate the pivots using VBA.</p>
  
  <p>I have already generated the pivot tables just fine and can manually manipulate them to create the relevant charts. As I create the tables (all on one sheet), I am labeling them (if that helps solve my problem). Where I am running into trouble is creating the pivot charts, and specifically, setting the data source. </p>
  
  <p>Since the charts are based on pivots that may change in size depending on the data present, I can't just assign a fixed range as the source. Same problem with a named range, as I still don't know what range to assign a name, unless I can assign a PT as a range.</p>
  
  <p>I've tried something like this, but that didn't work, since I'm not looking at an actual 'Range' object:</p>
  
  <pre><code>Dim MyChartObject As ChartObject
  Dim MyChart As Chart
  
  'These variables are assigned elsewhere in the code.
  Set MyChartObject = wksMainCharts.ChartObjects.Add( _
      iChartLeft, iChartTop, iChartWidth, iChartHeight)
  Set MyChart = MyChartObject.Chart
  MyChart.SetSourceData wksMainPivot.PivotTables(PivotName).SourceData 'Fails; requires Range object
  'More code to follow...
  </code></pre>
  
  <p>I do know that this is a string representation of the wrong range, but I was playing with what I could figure on my own. </p>
  
  <p>Essentially what I want is the <strong>opposite</strong> of <a href="https://stackoverflow.com/q/4079081/698590">this question</a>, which seeks to find the pivot table at a specified range. I also reviewed <a href="https://stackoverflow.com/q/13055801/698590">this question</a>, but it's not quite what I'm looking for (there are also no answers provided).</p>
  
  <p>Also: I'm asking here for a way to get the range of a PT, which I think would be interesting to know, but ultimately, I just want to create the pivot chart, so if anyone wants to offer a better method than what I've started on, I'm completely open to that as well.</p>