Forum Discussion

Copper Contributor

Nov 25, 2019

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

Macros and VBA

JonHager97

Copper Contributor

Aug 18, 2025

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?

HansVogelaar

MVP

Aug 18, 2025

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)