Forum Discussion
Prevent external reference in pivot Data Source
Hi, I'm trying to prevent external references being created in new copies of an existing spreadsheet. The issue seems to be limited to the Data Source for pivot tables. My spreadsheet includes var...
[Update]
After more digging, I think the issue relates to the pivot table cache (rather than the pivot table) staying linked to the old version of the workbook, as described at https://www.encyclopedia-excel.com/how-to-update-pivot-table-data-sources-on-new-copied-workbooks-with-vba .
I also think I MIGHT be able to solve the issue without vba, by setting Number of items to retain per field box to None , as described at https://support.microsoft.com/en-us/office/items-that-may-have-cached-data-found-27cd3af4-efd7-4bb4-bee8-46315a2f83cc .
Can anyone advise/confirm?