Cannot run Pivot table with macro

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.

Answer 1

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.