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