How To Create Pivot Table Using VBA

How to create a pivot table using VBA? Assuming that the data for the pivot table is laid out like this, then all you need to create a pivot using vba are there four lines of code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= ActiveSheet.UsedRange).CreatePivotTable TableDestination:="",  TableName:= "PivotTable1", DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1)

ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Name"

ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").Orientation = xlDataField

The pivot table so created using the VBA code above can be further modified. You can make a field appear in the row or field columns by using the following codes.

Say we have a field labeled “my_field_name“. The first one move a field to the row position.

With ActiveSheet.PivotTables("PivotTable1").PivotFields("my_field_name")
  .Orientation = xlRowField
 .Position = 2
End With

The below code will move the field to the column position.

With ActiveSheet.PivotTables("PivotTable1").PivotFields("my_field_name")
 .Orientation = xlColumnField
 .Position = 1
End With

And finally, this one will move a field to the data field.

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("my_field_name"), "Sum of my_field_name", xlSum

Read more about reading data from pivot table

What Do You Think ?

Comments and Trackbacks

Vamsi wrote:

vba code is very good

October 31st, 2009 at 2:30 pm
Ajay wrote:

Thanks – Glad you found it useful

November 1st, 2009 at 1:30 am
fredy wrote:

thanks for code, nice…

saludos!!!

April 23rd, 2010 at 8:46 am
Jaconaut wrote:

How do you prevent it from creating a new sheet for the table?

I was able to move the table over to an exising sheet, but not make that the original destination….

Changing the TableDestination:=”" to TableDestination:= Sheets”Weather-O-Matic” fails.

Heres my code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=ActiveSheet.UsedRange).CreatePivotTable TableDestination:=”", TableName:=”WeatherTable”, DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard TableDestination:=Sheets(“Weather-O-Matic”).Cells(2, 2)

ActiveSheet.PivotTables(“WeatherTable”).AddFields RowFields:=”DMA Name”

ActiveSheet.PivotTables(“WeatherTable”).PivotFields(“DMA Name”).Orientation = xlDataField

ActiveWorkbook.ShowPivotTableFieldList = False

Sheets(1).Delete

September 23rd, 2010 at 6:25 am
Sunil wrote:

While recording pivot table in VB of excel , this will ask to insert a new sheet, as an when i run macro in the same workbook the sheets name changes EX sheet 1,2,3,4, this causes , is there any VB to save the pivot table to already exsisting sheet .

October 14th, 2010 at 8:19 am
Mohsin wrote:

Subject::Help Required
B/m code is fine for 65536 number of rows or less.But when number of rows exceeds 65536, it gives an error”Type Mismatch”.
Please help in this regard.I am using Excel 2010.

Sub test()
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ActiveSheet.UsedRange, Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:=”", TableName:=”PivotTable8″, DefaultVersion:=xlPivotTableVersion14
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1)
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.ChartArea.Copy
Sheets(“Sheet2″).Select
ActiveSheet.Paste
End Sub

March 10th, 2011 at 1:39 am
vincenzo landi wrote:

Hello, i have a macro similar to one in the example. I want to know if it is posible save this macro as add.in to be used in all worksheet
Thank you

April 1st, 2014 at 12:11 pm