Consulting

Page 4 of 4 FirstFirst ... 2 3 4
Results 61 to 66 of 66

Thread: Help Required : Random Sampling of Data

  1. #61
    VBAX Tutor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    232
    Location
    Thanks Paul!
    I'm getting a run time error '5': Invalid procedure call or argument with create pivot table code
    Could you please check and help me

  2. #62
    VBAX Tutor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    232
    Location
    It is working when I changed the version from 7 to 15. Hope that is fine
        ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rData, Version:=xlPivotTableVersion15).CreatePivotTable _
            TableDestination:=wsTemp.Cells(1, 1), TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion15

  3. #63
    VBAX Sage
    Joined
    Apr 2007
    Posts
    8,335
    Location
    Yea, I forgot to mention that the PT version seems to be dependent on different users

    Glad you sorted it out. I never really figured out what was the best way to handle it since the macro recorder always put in

    DefaultVersion:=7


    https://docs.microsoft.com/en-us/off...bleversionlist

    Specifies the version of a PivotTable or a PivotCache. Creating PivotTables with a specific version ensures that tables created in Excel behave in the same manner as they did in the corresponding version of Excel.

    Name Value Description
    xlPivotTableVersion2000 0 Excel 2000
    xlPivotTableVersion10 1 Excel 2002
    xlPivotTableVersion11 2 Excel 2003
    xlPivotTableVersion12 3 Excel 2007
    xlPivotTableVersion14 4 Excel 2010
    xlPivotTableVersion15 5 Excel 2013
    xlPivotTableVersionCurrent -1 Provided only for backward compatibility
    Last edited by Paul_Hossler; 09-27-2021 at 03:37 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #64
    VBAX Tutor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    232
    Location
    Hi Paul,
    Hope all is well at your end.
    I recently added this tool as an excel add-in and added it to my Quick Access Toolbar (QAT). And then if I'm using it from the QAT/Add-in, I'm getting the error as mentioned in #61 with the below code. It is working fine in the normal version/other than add-in version.
        ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rData, Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:=wsTemp.Cells(1, 1), TableName:="Stastistics", DefaultVersion:=xlPivotTableVersion15
    I'm not sure what is the issue here. Could you please help me on this?
    I'm unable to attach the 'xlam' version of the file here
    Thanks,

  5. #65
    VBAX Sage
    Joined
    Apr 2007
    Posts
    8,335
    Location
    Try replacing "ThisWorkbook" with "ActiveWorkbook" and let me know

    There might be other instances of "ThisWorkbook" that need to be changed
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #66
    VBAX Tutor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    232
    Location
    Thanks Paul, it is working now

Posting Permissions

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