Changing data source of pivot table not working

I have had the same issues – VERY frustrating!  My work around is this:

1. Before you create your new tab, put your data into a Table. (Insert, Table, select your data range for the Table).
2. Now, look at the Source data of your pivot table – it should be pointing to the TableName (mine autopopulated the name to be Table1).  (If it’s not, change it).
3. Copy your tab to a new tab.
4. In the new tab, change the source data of the pivot table to the new table name in your new tab (mine autopopulated as Table13)
5. Voila!  The source data ‘sticks’.
6. Going forward, all you should need to do is steps 3 and 4.  The key seems to be putting your data into a Table.

Hi Sherry,

If an Office application is not working properly, restarting the application or the computer may resolve the issue. However, if it doesn’t work, we suggest that you do an online repair on your Office program. To do this, close all your Office programs and follow the steps below:

1. Go to Control Panel and click Programs.
2. Select Programs and Features, right-click Microsoft Office 365, and click Change.
3. Choose Online Repair and click Repair.
4. Once you’re done repairing the program, restart the computer and check if the issue still occurs.

Let us know what happens after doing an online repair.

Hi There,

I've come across similar issue at work. The pivot tables in same spreadsheet would behave normal in some pc's but not on others. Windows / Office updates causing the issue will explain the phenomenon.

It makes me feel nervous to apply updates. Genevieve, is Microsoft looking into this issue at all?

Paul.

Hello,

The advice above (updating windows) does not solve the problem.  I believe it was a Windows update that caused the problem.  The problem started immediately after a Windows update on Monday that I ran.  I've never gotten this error message in the 22 years I've been using pivot tables, and in this specific instance the 5 years I've been using this particular spreadsheet.  

For some reason Excel wants the actual workbook to be open before refreshing the pivot table.  In the past all the pivot tables refreshed even if the source workbook was closed. 

I believe the problem is related to a Windows update and is also hard coded into any spreadsheet that is made after that update.  I've recovered from the problem, but here is what I've done to get there:

1. Tried MS Office 365 "repair" unsuccessfully.  This caused my entire MS Office suite to think I was using it for the first time (all settings lost and product activation code lost).  My IT department had to intervene and this cost me all of yesterday morning.
2. Uninstalling and reinstalling Office 365==> also not effective.
3. Rolled back updates to last Friday's build ==> this helped.  The spreadsheet edited with the updated version of Windows still had the problem.  I tried it on my employees computer which is running Win 7 and an older version of Excel and got the same error message on him, so I'm confident it's a problem that got baked in the the spreadsheet itself and not the actual OS.  I recreated the spreadsheet from scratch and now the problem is gone.  I do not plan on installing whatever updates came out between 7/12 & 7/19 just to be safe going forward.

I hope this helps.  This was a major issue as we rely on Excel as part of our MRP process and this causes delays in ordering critical parts for our product.

Ok.  I tried the "Quick Repair" and that didn't fix the issue.  I then tried "Internet Repair" and instead of repairing it, it uninstalled it and then stopped.  I had to reinstall, which it wouldn't do until I got tech support from Microsoft involved.  After finally getting that done, I opened up the spreadsheet and it still does the same thing.