Pivot Table refresh changes the data source

[Stacy2927]

I've never had this problem before, I have a workbook that has about 15-20 tabs each with a pivot table derived from the same source. The data source range is 'Data Download'!$A$1:$I$10000. After refreshing the data source changes to 'Data Download'!$A$1:$E$1000 and any fields that were being used in the pivot table from columns F-I are lost. Any ideas on what I can do to stop it from changing?

 

[mamady]

Hi,

Is the workbook format xls? if yes, save it as xlsx and check.
Alternatively, it always better to have a dynamic range to avoid data source issue. Basically go the tabs, select the entire range and press Windows+T.. then let that table name be the source of your pivot table.

Best Regards
M. Yusuf

 

[Stacy2927]

HI,
The file extension is xlsm.

 

[mamady]

Hi,

If it has macro, then save it as xlsxm and try.

Alternatively, try the other suggestion I advised above about dynamic table

Regards
M. Yusuf