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
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
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
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
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.
I'm not sure what is the issue here. Could you please help me on this?ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rData, Version:=xlPivotTableVersion15).CreatePivotTable _ TableDestination:=wsTemp.Cells(1, 1), TableName:="Stastistics", DefaultVersion:=xlPivotTableVersion15
I'm unable to attach the 'xlam' version of the file here
Thanks,
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
Thanks Paul, it is working now