locked
Cannot run Pivot table with macro RRS feed

  • Question

  • I have create a simple database in Excel and want to generate pivot table report. But who will use this database is not expert in MS excel. So I have to create a ready made format of report via pivot table. But When I tried to run pivot table with macro an debugging session was started. The bugged yellow marked code is given bellow:

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Table1", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination _
            :="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion:= _
            xlPivotTableVersion12

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
        Range("Table1[[#All],[Name]:[Weekly Total]]").Select
        Sheets.Add
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Table1", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination _
            :="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion:= _
            xlPivotTableVersion12
        Sheets("Sheet4").Select
        Cells(3, 1).Select
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name")
            .Orientation = xlRowField
            .Position = 1
        End With
        ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("Nutrition"), "Sum of Nutrition", xlSum
        ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("Test"), "Sum of Test", xlSum
        ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("Medicine"), "Sum of Medicine", xlSum
        ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("Delevary cost"), "Sum of Delevary cost", xlSum
        ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("Other"), "Sum of Other", xlSum
        ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("Weekly Total"), "Sum of Weekly Total", xlSum
    End Sub
    I am unfamiliar with VB coding. Please need help.


    • Edited by Ireen Adler Saturday, December 7, 2013 7:28 PM
    Saturday, December 7, 2013 7:24 PM

All replies

  • In one sheet pivot table name must be unique.As your macro already created a pivot table "Pivot Table1", you can't use that name again.

    See if below helps....

    Sub PivotUser()
      
        Dim PT As PivotTable
        Dim PC As PivotCache
        
        Set PC = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:="Table1", Version:=xlPivotTableVersion12)
        
        Sheets.Add
        
        Set PT = PC.CreatePivotTable(TableDestination:=ActiveSheet.Name & "!R1c1", _
            TableName:=ActiveSheet.Name, DefaultVersion:=xlPivotTableVersion12)
        
        With PT
            .PivotFields("Name").Orientation = xlRowField
            .PivotFields("Name").Position = 1
        
            .AddDataField .PivotFields("Nutrition"), "Sum of Nutrition", xlSum
            .AddDataField .PivotFields("Test"), "Sum of Test", xlSum
            .AddDataField .PivotFields("Medicine"), "Sum of Medicine", xlSum
            .AddDataField .PivotFields("Delevary cost"), "Sum of Delevary cost", xlSum
            .AddDataField .PivotFields("Other"), "Sum of Other", xlSum
            .AddDataField .PivotFields("Weekly Total"), "Sum of Weekly Total", xlSum
        
        End With
        
    End Sub


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Monday, December 9, 2013 6:38 AM
    Answerer