Pivot Table : Remove File reference from Data Source - BUG
The Pivot tables created in a workbook - With a Named Range reference or Direct Cell range reference to a worksheet of the same workbook - does NOT work when the File is copied with a different name.
In the copied file, (Analyze, Change Data Source), contains the path of original file name. This causes a problem when a template workbook is distributed to many users. Even if the Named Range is restricted only to the Workbook or even a specific worksheet. It still stores the Workbook reference in the Data Source. I also tried "Save as" with different file name. Still it doesn't work.
I don't see any reason to retain the File name / path, if the Data source is from within the same workbook.
So this is a Bug which needs to be fixed.