Forum Discussion

WayneEK's avatar
WayneEK
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
Shannon8000's avatar
Shannon8000
Copper Contributor
Nov 06, 2020

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?

  • bsand210801's avatar
    bsand210801
    Copper Contributor
    May 18, 2021

    Shannon8000 

    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

    • RichardSeeley's avatar
      RichardSeeley
      Copper Contributor
      Dec 02, 2021

      bsand210801 

       

      I am running into same error in Microsoft 365. Was there ever a fix?

      • ninjaryder's avatar
        ninjaryder
        Copper Contributor
        Mar 05, 2024

        RichardSeeley 

         

        I have a similar issue noting that I'm trying create 1 pivot table (template) with the same edits in font size, filter, and column width edits, and column title change),  so the macro will duplicate the table 6 times into 6 new sheets.  Then I'd like to rearrange the categories in the Table Row within PivotTable Fields to look differently at the table data in each sheet..

         

        VBA runs, creates the 1st pivot, but on the copy/paste to a new sheet step,  it seems to drop the very 1st row category inside the PivotTable Fields section (noting it was there when macro was created and is within the VBA script-hence the error)

         

        "code scan" is not even generated under the first Table created from the macro. 

        ***Even the 1st PV created (3rd image) doesn't contain the 1st row category (Code Scanned Y/N) noted in the macro VBA code for position 1 (1st image)

         


        *** the missing category propagates upon creating the next copied table noting it could not find position 2 category name.

         

         

        Does something happen when you rearrange the categorical values in the Rows box of PivotTable Fields?   Even though they are 6 independent tables since they were copied/pasted?  

         

Resources