Excel VBA Tutorial - Using VBA to create PivotTable and PivotChart

 

Using VBA to create PivotTable and PivotChart | Cemtech Training Center Puchong, Selangor Melaka

 

I conducted an Excel VBA training last week. There is a missing chapter about PivotChart. So, I wish to take this opportunity to demo how to use VBA to create PivotTable and PivotChart.

 

1. Open an Excel Macro file named pivottable.xlsm.

 

Note: For Cemtech Fans, please go to Excel VBA tab below Cemtech Wall to download the file.

 

Using VBA to create PivotTable and PivotChart | Cemtech Training Center Puchong, Selangor Melaka

 

2. Pick any cell within the data range. Then, Ctrl + SHIFT + 8 to select the entire data.

 

Using VBA to create PivotTable and PivotChart | Cemtech Training Center Puchong, Selangor Melaka

 

3. Go to Name box, type mydata and hit ENTER. We will use this named range as the source data for the pivot table.

 

Note: This creates a name called mydata.

 

Using VBA to create PivotTable and PivotChart | Cemtech Training Center Puchong, Selangor Melaka

 

4. Press ALT + F11 to open Visual Basic Editor.

 

Note: Visual Basic Editor opens.

Using VBA to create PivotTable and PivotChart | Cemtech Training Center Puchong, Selangor Melaka

 

5. In the Project Explorer (the panel at the left), right-click and choose Insert > Module.

 

Using VBA to create PivotTable and PivotChart | Cemtech Training Center Puchong, Selangor Melaka

 

Note: This will create an empty module. Ready for us to insert VB code.

 

Using VBA to create PivotTable and PivotChart | Cemtech Training Center Puchong, Selangor Melaka

 

PivotCache is the data where the PivotTable is based on. Within a PivotTable, it can have several fields as the Row Field, Column Field or Data Field. I plan to create a PivotCache based on named range "mydata" (see above). Then, create a PivotTable based on PivotCache. In the PivotTable, i wish to show Employee Name as the row field, Sex as the column field and lastly basic salary as the data field. So, i need 3 pivotFields.

 

6. In the Module, type the following VB code to declare the object variables.

 

Dim mysheet As Worksheet  'to represent Sheet1 worksheet

Dim pc As PivotCache    'for PivotCache object

Dim pt As PivotTable      'for PivotTable object

Dim pf1 As PivotField     'for Employee Name row field

Dim pf2 As PivotField     'for Sex row field

Dim pf3 As PivotField     'for basic salary data field

 

7. Create a subroutine called CreatePivotTable as follows. First create a PivotCache from the named range "mydata". Use the PivotCache to create PivotTable at position A15. Name it as ItemList. Set EmployeeName as the row field, EmpSex as the column field, and Employee Basic Salary as the Data Source. This routine will create the Table.

 

Sub CreatePivotTable()

'create a pivotcache object using datasource from mydata name

Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "=mydata")

 

'create a pivottable at location A15 and name the pivottable as ItemList

Set pt = pc.CreatePivotTable(Range("A15"), "ItemList")

 

 

Set pf1 = pt.PivotFields("EmpName")

pf1.Orientation = xlRowField

 

Set pf2 = pt.PivotFields("EmpSex")

pf2.Orientation = xlColumnField

 

Set pf3 = pt.PivotFields("EmpBasicSalary")

pf3.Orientation = xlDataField

 

CreatePivotChart

End Sub

 

 

8. In the last line of CreatePivotTable, it call another subroutine called CreatePivotChart. First, create a ChartObject at position 300 left, 220 top, 550 width, 200 height. Then reference the pivotchart from ChartObject. Set the source data of the the pivotchart to the pivottable. Set the chart type to 3D Column. Finally set the name to EChart1.

 

Sub CreatePivotChart()

Dim chobj As ChartObject

Dim ch As Chart

Set mysheet = Sheets("Sheet1")

Set chobj = mysheet.ChartObjects.Add(300, 200, 550, 200)

'300 left, 220 top, 550 width, 200 height

 

Set ch = chobj.Chart

ch.SetSourceData pt.TableRange1

ch.ChartType = xl3DColumn

chobj.Name = "EChart1"

End Sub

 

9. Then, run the code CreatePivotTable.

 

Using VBA to create PivotTable and PivotChart | Cemtech Training Center Puchong, Selangor Melaka

 

Note: I list out the whole code for your reference.

Option Explicit

 

Dim mysheet As Worksheet

Dim pc As PivotCache

Dim pt As PivotTable

Dim pf1 As PivotField

Dim pf2 As PivotField

Dim pf3 As PivotField

 

Sub CreatePivotTable()

'create a pivotcache object using datasource from mydata name

Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "=mydata")

 

'create a pivottable at location A15 and name the pivottable as ItemList

Set pt = pc.CreatePivotTable(Range("A15"), "ItemList")

 

 

Set pf1 = pt.PivotFields("EmpName")

pf1.Orientation = xlRowField

 

Set pf2 = pt.PivotFields("EmpSex")

pf2.Orientation = xlColumnField

 

Set pf3 = pt.PivotFields("EmpBasicSalary")

pf3.Orientation = xlDataField

 

CreatePivotChart

End Sub

 

 

Sub CreatePivotChart()

Dim chobj As ChartObject

Dim ch As Chart

Set mysheet = Sheets("Sheet1")

Set chobj = mysheet.ChartObjects.Add(300, 200, 550, 200)

'50 left

'120 top

'550 width

'200 height

 

Set ch = chobj.Chart

ch.SetSourceData pt.TableRange1

ch.ChartType = xl3DColumn

chobj.Name = "EChart1"

End Sub

 

Note: For Cemtech Fans, please go to Excel VBA tab below Cemtech Wall to download the file.

 

By Liang Ee Hang  | Google  | LinkedIn

 

Cempaka Technology Sdn Bhd

Pusat Latihan Komputer Cempaka
~Your HRDC Premiere Training Provider~
64-2, Jalan Puteri 2/2, Bandar Puteri Puchong, 47100 Puchong Selangor, Malaysia. Tel: 603-80684461
1-28, Jalan PM4, Plaza Mahkota, 75000 Melaka. Tel: 606-2835955
Instant SSL