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
No comments:
Post a Comment