Runtime err 5: Invalid procedure call or argument

[SIFS]

Dear Sirs,

Using Excel, I recorded a macro to create a pivot table. When I try to run the recorded macro again, I have "Runtime error 5: Invalid procedure call or argument". The text of the line where error exists is below:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Consolidated AMC!R1C1:R67C14", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="Monthly total!R3C1", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion12

The error is coming in the blue text. Can anyone please help me how to fix it. Thanks.

 

[AlphaFrog]

It has an error the because the second time you run the macro, "PivotTable2" already exists on sheet "Monthly total!R3C1". You can't create another "PivotTable2" in the same location.

How to fix it depends on what your end goal is. Do you want to delete the previous "PivotTable2" and create a new one? Do you want to create a new pivot table somewhere else on sheet Monthly total?

If you want to recreate "PivotTable2", this will delete it if it already exists. Put this before you try to create "PivotTable2".

    On Error Resume Next
    Sheets("Monthly total").PivotTables("PivotTable2").TableRange2.Clear
    On Error GoTo 0

Recording the steps of creating a pivot table is a good way to get the base code to create and manipulate pivot tables, but it almost always requires additional modification to get to to do what you really want.

 

[SIFS]

Dear AlphaFrog

Thanks for your reply but I still has problem. I want to delete the previous table and create a new table after some changes happened in my database worksheet. Could you please tell me where exactly i have to put the code you mentioned in your post? Thanks

 

[Derek Brown]

Duplicate post?
http://www.mrexcel.com/forum/showthread.php?t=505600

 

[SIFS]

I posted duplicate as I am new and am not much familiar with it. So posted on different forums.

 

[AlphaFrog]

    On Error Resume Next
    Sheets("Monthly total").PivotTables("PivotTable2").TableRange2.Clear
    On Error GoTo 0

   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
   "Consolidated AMC!R1C1:R67C14", Version:=xlPivotTableVersion12). _
   CreatePivotTable TableDestination:="Monthly total!R3C1", TableName:= _
   "PivotTable2", DefaultVersion:=xlPivotTableVersion12

If the sheet Monthly total only has the pivot table and nothing else, you could try this to clear everything from the sheet...

    Sheets("Monthly total").Cells.Clear

   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
   "Consolidated AMC!R1C1:R67C14", Version:=xlPivotTableVersion12). _
   CreatePivotTable TableDestination:="Monthly total!R3C1", TableName:= _
   "PivotTable2", DefaultVersion:=xlPivotTableVersion12

 

[SIFS]

Many thanks