Create VBA Pivot Table

ggilzow

New member
Joined
Jun 10, 2013
Messages
1
Reaction score
0
Points
0
I am trying to create a VBA pivot table to count the number of times column C occures for each year.
Here is the code I am trying to use. For some reason the code is not collecting the data and compiling into a pivot table. Please help me in fixing this code.


HTML:
Sub ptable()Application.ScreenUpdating = FalseApplication.DisplayAlerts = False
' delete sheet Pivot Table 1 if present in workbookOn Error Resume Next
Sheets("Pivot Table 1").DeleteSheets.Add After:=Sheets(Sheets.Count)ActiveSheet.Name = "Pivot Table 1"
Sheets("Sheet1").Select' selecting the source data in sheet name dataActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _Sheets("Sheet1").Range("b1:c" & Sheets("Sheet1").Range("a65356").End(xlUp).Row)).CreatePivotTable TableDestination:=Sheets("Pivot Table 1").Cells(1, 1), _TableName:="PivotTable1"Sheets("Pivot Table 1").Select' add the row fieldWith ActiveSheet.PivotTables("PivotTable1").PivotFields("Vendor").Orientation = xlRowField.Position = 1End With
' adding column fieldWith ActiveSheet.PivotTables("PivotTable1").PivotFields("Value").Orientation = xlColumnField.Position = 1End WithActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _"PivotTable1").PivotFields("Value"), "Sum of Value", xlSumWith ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").Orientation = xlColumnField.Position = 1End With' change it to true if you want to show grand totalActiveSheet.PivotTables("PivotTable1").RowGrand = True' change it to true if you want to show grand totalActiveSheet.PivotTables("PivotTable1").ColumnGrand = True
'hide field list windowActiveWorkbook.ShowPivotTableFieldList = FalseApplication.ScreenUpdating = TrueApplication.DisplayAlerts = True
End Sub
View attachment Master Workbook Rev 12.xlsm
 
Top