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 scre...
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)