Create and link pivot chart to pivot table !!!!

[adr12345]

I have written a code for creating pivot table. After this I want to add Chart which is linked to PT1 pivot table. and I should be able to give exact cell location where that chart should appear.

Can anyone help how to do it? Its kinda urgent!!!



Set data1 = ActiveWorkbook.PivotCaches.Create(xlDatabase, range("A1", Cells(B, A)).Address(, , xlR1C1))
Set PT1 = data1.CreatePivotTable(Worksheets("Tables").range("B3"))

Set PT1 = data1.CreatePivotTable(Worksheets("Tables").range("B83"))

With PT1
With .PivotFields("name")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Job type")
.Orientation = xlRowField
.Position = 2
End With
With .PivotFields("Multiple Bids")
.Orientation = xlColumnField
.Position = 1
End With
With .PivotFields("ASR Id")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
.Caption = "Volume"
End With

With .PivotFields("Annual Cost")
.Orientation = xlDataField
.Function = xlSum
.Position = 2
.Caption = "Annual Sum"
End With

With PT1
.MergeLabels = True
.InGridDropZones = True
.RowAxisLayout xlTabularRow
.SmallGrid = True
.ShowTableStyleColumnHeaders = True
.LayoutRowDefault = xlTabularRow
End With

 

[Domenic]

The following code adds a pivot chart on the same worksheet as the pivot table. Try adding the following code at the end of the existing one...

[COLOR=darkblue]Dim[/COLOR] oChart [COLOR=darkblue]As[/COLOR] Chart

[COLOR=darkblue]Set[/COLOR] oChart = PT1.Parent.Shapes.AddChart(XlChartType:=xlLine).Chart

oChart.SetSourceData Source:=PT1.TableRange1

Change the chart type, accordingly.

Hope this helps!

 

[adr12345]

Hi Domenic,

Thanks it is working.

However is there any method by which I can specify the cell location for that graph?
Coz I have multiple tables and if I repeat this code for all tables then all the Graphs are overlapping on each other.

Or can the code itself identify all the pivot tables in sheet and generate graphs for all the pivots ? using some kind of loop..


Thanks !!

 

[Domenic]

Try...

Set oChart = PT1.Parent.Shapes.AddChart(XlChartType:=xlLine, Left:=Range("D2"), Top:=Range("D2"), Width:=350, Height:=200).Chart

Note, the width and height are optional.

Hope this helps!

 

[adr12345]

Left and top will decide the location of that chart right?

Thanks a tons..
M half way through my automation now yayyyy

 

[Domenic]

Yep, that's right...