Thread: bug in vba macro to create pivot table

I am trying to automate creating a pivot table. I get a feed everyday from another department that contains 40 columns of data. I need to run same pivot table every day using 3 of these columns only (column B, J, & N)

Right now it is a very manual process.

The first pivot table is for column B & J where column B is row information of pivot table and column J is the data information.

The second pivot table is for column B & N where column B is row information of pivot table and column N is the data information.

Once I have both tables created, I then manually cut and paste information onto a separate sheet called Analysis.

Is there a way to do this manually? I have created a Macro but I keep getting a bug error and can’t figure out what is wrong with my code.

I have attached the spreadsheet of what I want the end result to be. The end result is sheet “Analysis”.

Thanks for any help.

Code:

 Sub CreatePivotTable()
'
' CreatePivotTable Macro
' Creates two Pivot Tables
'

'
    Columns("B:J").Select
    Application.CommandBars("PivotTable").Visible = True
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Trade Table'!C2:C10").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1"
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Account"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Net Amount")
        .Orientation = xlDataField
        .Caption = "Sum of Net Amount"
        .Function = xlSum
    End With
    Application.CommandBars("PivotTable").Visible = False
    Sheets("Trade Table").Select
    Application.CommandBars("PivotTable").Visible = True
    Columns("B:M").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Trade Table'!C2:C13").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable2"
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable2").SmallGrid = False
    ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Account"
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Commission")
        .Orientation = xlDataField
        .Caption = "Sum of Commission"
        .Function = xlSum
    End With
    Range("A1").Select
    Sheets("Trade Table").Select
    Range("A2").Select
End Sub