Creating a Pivot Chart Report Using VBA

A PivotChart represents the data in a PivotTable report. Using VBA code, you can create a PivotChart based on an existing PivotTable report, and you can change the layout and data displayed in a PivotChart just as easily as you can reformat a PivotTable report. Similar to PivotTable reports, PivotCharts are interactive and allow you to view data in different ways by changing the position or detail of the PivotChart fields. Excel creates a PivotChart report on a separate chart sheet. You may, however, copy the resulting chart to a worksheet to produce an embedded chart that can be viewed or printed on the same page with the PivotTable report. A PivotChart report is linked to a PivotTable report. This means that when you rearrange the data in a PivotTable report, the PivotChart report displays the same view of the data, and vice versa. The default chart type for a PivotTable chart report is a stacked column chart. This type of chart is useful for comparing the contribution of each value to a total across categories. You can generate any type of a PivotChart report, except xy (scatter), stock, or bubble.

Creating a PivotChart report programmatically boils down to using the SetDataSource method of the PivotChart object and specifying a reference to the PivotTable range. The PivotTable object has the following two properties that return ranges representing part or all of the PivotTable report:

■ TableRange1—Returns a range representing the PivotTable report without page fields

■ TableRange2—Returns a range representing the entire PivotTable report

The following procedure creates a PivotChart report based on the PivotTable report depicted in Figure A-10.

□ PivutlZlidrli-Hfe

^jDjxj

Ai

3

1 G |

D f it: I

1 f. 1

ft

H I

I | J |

K

L

_ *

1

2 |

3

IProcudNane

liflT r\

< .

si

Sum nf FyirrriiPInn

Hflfcspwsen ▼ |

6

Couriiv

.'"'. < J1 - Fi,|tl

jsne:

Leverlne Laura Cslfehan

t H'.-: i. . i -ij'l jt! 1J.IV y H

NercvDavob Rctert I'iu

SI even Bucnersn

OrBnO Total

7

47?

9« J

3192

2753.5

1329.4.

76

666.7

15356

6119.1

J |

Bstfum D---.ll

IfiFfRClH 2866.5 6665.03

233837 _1910_

71341 35 255.33 9162.59

irfmi 59

17H59 F6 • .353' : _17770.57.

1209 6561.47

171167 77 7326 29459.37.

75745'5 46-115 62002

7674.5 14707.97

13300383 33B21 85

1L

Ulie.SBj

103925.70

"

Par nat

FCRi 9 2515.7

9HF Fi

1315673 156-127

1314.35

4R79 nf. 17291.61

331? 33 738

9E01 41 tt.7'-

il?10 Sfi 211-163

sni!i6 7R 32561 61

1-

Frirril

£676.03

159D.se

95766

4131 .V

2117.7

270

1626.9

6*2

1333.2

13610 65

14

Frnnoe

5434.39

3379 73

15371 13

5359

wsn B

4q7n.TR

13487 4*

25669

7933 5=

313=831

GermejY

55627.17

15753.53

■1597361

26-197.97

56341.52

9316.93

24611 63

7BG8S3

SCHS.54

23326168

it

k eland

1C034.96

7-3036

1661564

13V3.32

1336 4

7556

2519

2566.76

39979.9

16

My

£477 09

5H38

RR

<WR99

39FF-

553

1o:s

1RE13H

15770,15 2356267

1'=

Noway

622.35

2CC4.4

17294j

700

5735.15

3D I

Pfhui

(596

10191 F1TR

9f3S=i

isn

3531 95

21

Ported

576

£37.5

2333.4

1454.53

151924

265:2

1274.75

1147268

22

Span

977,5

224

3375.25

206

7723.CS

1241

1661.1

2363.3C

176632

I

5<Av;rtnri SiVil26tta"ii3

BTO5.7

75

11 450*1963

0303 9?

1335 3

331053

7401 13

535544

1 SOI ,33

53466'4 3156261

fl

UK

~ 3411.3

6336.55

3CCG.1

9319.6

6751.11

3527.5

10645.73

SaCS.43

5101.66

569713

7F

IIFLi

33054,35

17333 71

339F4 46

¿6755«

4F.ila 53

17916 05

¿3761 .31

739=0 45

15173 ns

345564 59

Yenaztete

2668.* a

378

112-16.37

9903.3

8103.ES

317352

925263

7175.7

•1607.95

5301063

_

2L

Orfrid Iclfl

166537.75

77336 C4

»261262

136662.27

i32W3.es

73913.13

192107.56

12456622

C6792.25

123576267

2Z

_J i il

i » h 5ales Campatlson >,5beetl

¿SheetZ /5hwt3 /

\li_

Figure A-10: This PivotTable report is used to graph data in the PivotChart report.

Sub CreatePivotChart() Dim myChart As Chart Dim strChartName As String Dim rngSource As Range Dim pvtTable As PivotTable

Set pvtTable = Worksheets("Sheet1").PivotTables(1) Set rngSource = pvtTable.TableRange2 strChartName = "Sales Comparison" Set myChart = ActiveWorkbook.Charts.Add With myChart

.Name = strChartName .SetSourceData Source:=rngSource .ChartType = xlColumnClustered

End With

' set the current page for the PivotTable report to the ' page named "Tofu"

pvtTable.PivotFields("ProductName").CurrentPage = "Tofu" End Sub

After creating a PivotChart report based on the PivotTable report in Sheet1 (Figure A-10), the procedure changes the current page for the PivotTable report to display information about the product named Tofu. The resulting PivotChart report is shown in Figure A-11. Notice that the fields in a PivotChart report are shown as drop-down lists to allow you to play with the data on the chart.

Figure A-11: The PivotChart report is generated from the PivotTable report data on a separate chart sheet.

The following procedure lists the names of the PivotChart report fields to a worksheet:

Sub GetChartInfo()

' list of all the PivotTable field names used ' in the first PivotChart report

Dim sht As Worksheet Dim r As Integer Dim fld As PivotField

Set sht = Worksheets.Add sht.Activate r = 1

For Each fld In _

Charts("Sales Comparison").PivotLayout.PivotFields sht.Cells(r, 1).Value = fld.Caption r = r + 1 Next fld End Sub

0 0

Post a comment