Get the original range of a pivot table

I am creating a set of reports that will be updated daily for some users who are not super modern when it comes to pivot charts and pivot tables in Excel (2010). I have data from an already automated output from our db, but to make this information useful (and easier to use), I want to generate pivot points using VBA.

I've already generated pivot tables just fine and can manually manipulate them to create the appropriate charts. When I create tables (all on one sheet), I label them (if that helps solve my problem). Where I ran into difficulties, you create pivot charts and, in particular, set the data source.

Since the charts are based on centers, which can change in size depending on the data available, I cannot simply assign a fixed range as the source. Same problem with named range as I still don't know which range to assign the name to if I can't assign PT as the range.

I tried something like this, but it didn't work since I am not looking at the actual Range object:

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...

      

        
        
        
      

    

I know this is a string representation of the wrong range, but I was playing with what I could on my own.

Essentially what I want is opposite this question , which seeks to find a pivot table in a specified range. I've also looked at this question , but that's not exactly what I'm looking for (no answers either).

Also: I am asking here to get the PT range which I think will be interesting, but ultimately I just want to create a pivot chart, so if anyone wants to suggest better than I started, I am completely open for this.