After updating PivotTable Source data with Macro, slicer doesn't show PivotTable

Hi, I put together a code from reference online to:

1. use input boxes to input the old source data
2. use input boxes to input the new source data
3. Macro will update all pivot tables which has the old source data with the new source data I input
4. Pivot tables will have the "savedata" option enabled and pivot tables will be refereshed

Please Login or Register  to view this content.

I am encountering two problems

1) My workbook consists of multiple pivot tables and some worksheets has the same pivot table referenced. When I enable the option pivot table save data, it makes the file really big. Otherwise, If i manually update each pivottable by clicking on ribbon >analyse >change source data, the file is not as big as when I ran the macro. Attached is the information I extracted
pivot table info.png


2) My workbook consists of multiple slicers and when I use the macro to update all my pivot tables, it does not show in "report connections" on my slicers anymore

slicer.png


Any help would be appreciated! Thank you!

Do you really need to update all those pivot table sources? Is it better to rename the source file so it matches the pivot table source data name then refresh all tables?

Hi, kersplash! Thank you for your reply. I will have to update all the pivot table sources because of the structure of my organisation. Every month, data files are generated from a database system and new data files are generated every month.

You might be able to use some sort of background process/manually to take your source file(s) and copy it to another location and then rename to match your pivot table source.

Just an idea.