I have an excel file with 10 tabs.  1 tab is called Data Summary.  I cut and paste data onto this tab. I have a pivot tables built off the Data Summary tab sorting the data in various ways in the other tabs.  The blank file template has been saved.  I pull up the template and get "Can't open Pivot Table source file (then file name of template file)"  I have tried checking "Prefer the Excel data model when creating pivot tables, ..."  This isn't an external data link of any type.  I have clicked on Connections>Break links just to be sure.  If I pull up the template directly, so the file name remains the same (as opposed to dropping the .xlmt extension and adding 1) everything works correctly. 

There was another method noted in the forum, I tried it to no avail.  What is the answer to this riddle? 

Hi,

For Data Source remain the same name, I think we can paste the data with value only. With this way we can give the table a new name as we want.

For external data issue, I have tested from my side. It seemed as long as you want to save a template file contain Pivot Table, Excel will consider the data as external data, even if you created a brand new table.

But to be sure about this, I want to confirm some information with you: 

  1. Did you get any messages when you were saving the file as template? 

  2. Did your source data contain any relationship with other tables?

  3. Did your source data have any column refererring to other tables/workbooks?

Do you mind sending me a sample of your Workbook to me:ibsofc@microsoft.com

If I misunderstood anything, please let me know. Thank you.