Create a simple pivot table using vba and hide sub total

If you want to create a new pivot table using vba and after creating a table you want to hide the sub total.

For example

Your Source data look likes below



You want pivot table to be like below-



Here is the code-

Sub simple_pivot_table2()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

' delete sheet Pivot Table 2 if preent in workbook
On Error Resume Next

Sheets("Pivot Table 2").Delete
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Pivot Table 2"

Sheets("Data").Select
' selecting the source data in sheet name data
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Sheets("Data").Range("a1:d" & Sheets("Data").Range("a65356").End(xlUp).Row)).CreatePivotTable TableDestination:=Sheets("Pivot Table 2").Cells(1, 1), _
TableName:="PivotTable1"
Sheets("Pivot Table 2").Select
' add the row field
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Vendor")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Segment")
.Orientation = xlRowField
.Position = 2
End With


' adding column field
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Value")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Value"), "Sum of Value", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Year")
.Orientation = xlColumnField
.Position = 1
End With
' hide subtotal
ActiveSheet.PivotTables("PivotTable1").PivotFields("Vendor").Subtotals(1) = False
' chnage it to true if you want to show grand total
ActiveSheet.PivotTables("PivotTable1").RowGrand = True
' chnage it to true if you want to show grand total
ActiveSheet.PivotTables("PivotTable1").ColumnGrand = True

'hide field list window
ActiveWorkbook.ShowPivotTableFieldList = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub


Excel Macro file-

http://www.filefactory.com/file/cc4e71c/n/pivot_tables_in_vba.xlsm