3

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.

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.

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.

I've tried something like this, but that didn't work, since I'm not looking at an 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 do know that this is a string representation of the wrong range, but I was playing with what I could figure on my own.

Essentially what I want is the opposite of this question, which seeks to find the pivot table at a specified range. I also reviewed this question, but it's not quite what I'm looking for (there are also no answers provided).

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.

Community
  • 1
  • 1
Gaffi
  • 4,307
  • 8
  • 43
  • 73
  • 2
    No need for VBA for that part: simply create a dynamic named range and use this name as the source for the pivot. See the answer in this post: http://superuser.com/questions/540307/a-way-to-automatically-determine-ranges-in-excel/540341#540341 – Peter Albert Jan 24 '13 at 22:02
  • Thanks for the link, @PeterAlbert. I may look into this in the future (it's a very cool concept!), but the data isn't being imported to my workbook. Rather, the data pull is generating an Excel file for each run. I then will run this VBA on those new files using an add-in. Sid's answer I think works best for me here. – Gaffi Jan 25 '13 at 13:25
  • See the answer to this question also: [How can I get the ListObject object of a PivotTable](https://stackoverflow.com/a/46109152/2712565) If the PivotTable is not linked to a formal _Table_ (ListObject), the **SourceData** property will return the _range_ of the data: `? activecell.PivotTable.PivotCache.SourceData` – GlennFromIowa Oct 18 '17 at 18:30

1 Answers1

6

Is this what you are trying to achieve?

Sub Sample()
    Dim Chrt As Chart, pvtTbl As PivotTable

    Set pvtTbl = ActiveSheet.PivotTables(1)

    Set Chrt = ActiveSheet.ChartObjects(1).Chart

    Chrt.SetSourceData Source:=pvtTbl.TableRange1
End Sub

So your this line 

MyChart.SetSourceData wksMainPivot.PivotTables(PivotName).SourceData

becomes

MyChart.SetSourceData wksMainPivot.PivotTables(PivotName).TableRange1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250