Forum Discussion
Macro Error for Pivot Table
I can create a Pivot Table for the data I download and it works fine.
However, when I made a Macro to do the same thing, I get an error. The error is: Run-time error '5':
The following is a screenshot of the highlighted error in the Macro. I am now using Excel 2013, however I made an initial Macro using an older version of Excel and am now trying to make a pivot table using 2013 and installing it near the start of the existing Macro I have.
Macro Error for Pivot Table
How do I correct for this error?
11 Replies
I am getting the same error (run time error 5). I record macros and create a pivot table; there is no problem creating the pivot table when I record it. Then I used the same exact code that was just recorded and it errors out with runtime error 5. Any solutions? Anyone?
Sometimes it helps to separate the code for creating the pivot cache and for creating the pivot table.
And you can let Excel assign a name to the pivot table, to avoid conflicts. For example:
Dim pc As PivotCache Dim pt As PivotTable Set pc = ActiveWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:="Table1", _ Version:=xlPivotTableVersion15) Set pt = pc.CreatePivotTable( _ TableDestination:=Worksheets("Sheet1").Range("J3"), _ DefaultVersion:=xlPivotTableVersion15)
WayneEK did you ever resolve this? I am getting a very similar message and it is indicating the bug is within the pivot portion. I built the macro using the record macro feature and not the line by line code. Any advice?
I ran into this same issue. In Excel 2013, the PivotTable version number is different than if you are using other versions.
When using Excel 2013, you must change Version:=6 to Version:=xlPivotTableVersion15. Change DefaultVersion:=6 to DefaultVersion:=xlPivotTableVersion15.
Excel 2013 VBA script for creating PivotTable:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R4000C6", Version:=xlPivotTableVersion15).CreatePivotTable TableDestination:= _
"Template!R3C1", TableName:="SalesTaxTable", DefaultVersion:=xlPivotTableVersion15
WayneEK Does "Table1" exist? And what about the destination "Contract1 Report"?
If either one of those doesn't exist, or is even spelled differently, then that would cause the code to bug.
Thanks for replying. I confirmed the spelling and reference to the sheet and table is correct but am still getting the error. The only thing I can think it might be is that I should create a separate Sheet to put the Pivot Table on, instead of the existing sheet that the table is on. Thanks anyways.
So long ago BUT did you ever resolve this?
