Confusion about Excel Update PivotTable Code

Question

Hello,

Either Friday or Saturday I recorded a macro, it generated this code:

Sheets.Add

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase,SourceData:="SumIfs!R1C1:R1241C8",

DefaultVersion:=6).CreatePivotTable TableDestination:="Sheet1!R3C1",

TableName:="PivotTable1",DefaultVersion:=6

Sheets("Sheet1").Select

Today I recorded a macro, it generated different code:

Sheets.Add
ActiveWorkbook.Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache. _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=8
Sheets("Sheet1").Select

The question:

I was under the impression that the pivot caches create method had source type as required, yet in the new macro there is no source type.

Though SourceData was optional, I don't understand how the new macro understands the source data sheet.

While the default version is optional, when and why did it change from 6 to 8

The net result, much of my older code is now generating run time errors since the latest Excel update that I did not ask for (or provide permission to change my computer's configuration).

Answer 1

Wow, just, wow, don't know where to begin with that response.

Find the person responsible for updating your website with accurate information: https://learn.microsoft.com/en-us/office/vba/api/excel.pivotcaches.create

Answer 2

Hi Glenn Harris2,

Thanks for your post in Microsoft Community.

Based on your description, it seems like that you're encountering VBA related issue.

Well, there is a better place to publish which is Stack Overflow.

Ask VBA programming questions to Stack Overflow by using the VBA tag along with any other relevant tags.

Please note that Stack Overflow includes guidelines, such as requesting a descriptive title, a complete and concise problem description, and sufficient details to reproduce your problem. Feature requests or questions that are too broad are considered off-topic.

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

I can't help you, but I'll leave this question open in case one of our great volunteers has ideas for you.

Best regards,

Thomas C - MSFT | Microsoft Community Support Specialist