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 Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    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 Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    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
    Location
    United States
    Posts
    8,711
    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 Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    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
    Location
    United States
    Posts
    8,711
    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 Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    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
  •