Results 1 to 2 of 2

Thread: Create VBA Pivot Table

  1. #1

    Create VBA Pivot Table

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 Code:
    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
    Master Workbook Rev 12.xlsm

  2. #2
    Excel 2010, Table, PivotTable, PivotChart
    Create PT with VBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts