dcsimg
Results 1 to 4 of 4

Thread: Pivot Table Macro Error

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2020
    Posts
    4

    Question Pivot Table Macro Error

    Hello,

    I'm trying to automate pivot table using a macro I recorded from an Excel 2013
    However, once i execute this macro on another PC with Excel 2010 it is always showing an error on this line


    Code:
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Tableau1", Version:=xlPivotTableVersion15).CreatePivotTable _
            TableDestination:="Feuil1!R3C1", TableName:="Tableau croisé dynamique1", _
            DefaultVersion:=xlPivotTableVersion15
    with surligned
    Code:
    xlPivotTableVersion15
    The two attempts i tired so far in reading the forum was:
    1) enable the Analysis Toolpak
    2) Enable the analysis toolpak - VBA

    I can post the full code if it would help. However it is quite long. Please let me know. Would love the help me running this macro on booth excell 2010 and 2013.

    Cheers

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,762

    Re: Pivot Table Macro Error

    try changing to an earlier version
    i would believe that the versions would be forwards compatible, not backwards, like version 15 would not exist in excel 2010, but you can use version 14 pivot table in excel version 15 (2013)
    or xlpivottableversioncurrent might be useful

    i have not tested any of the above
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Mar 2020
    Posts
    4

    Re: Pivot Table Macro Error

    Thanks for your answear.
    Dawn I didn't see that the argument appear twice.
    version 14 work on the excel 2010

    however the argument xlpivottableversioncurrent doesn't work :
    It display Running error 5: bad argument. I am not abble to make the same macro working on both excel 2010 and 2013


    Cheers.
    Last edited by benjibasson83; Mar 16th, 2020 at 03:22 AM.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,762

    Re: Pivot Table Macro Error

    the argument is optional so may not be required at all
    else you can check the excel version then pass the appropriate number

    Code:
    select case application.version
        case 15: pv = 5   ' excel 2013
        case 14: pv = 4   ' excel 2010
        '  other versions
        case else: msgbox "version not supported"
    end select
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Tableau1", Version:=pv).CreatePivotTable _
            TableDestination:="Feuil1!R3C1", TableName:="Tableau croisé dynamique1", _
            DefaultVersion:=pv
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width