I am running a macro which moves selected sheets from a main workbook to a new workbook. Some of the moved sheets include graphs (note: these are 'not' pivot charts), the data source for which are on the moved sheets but use named ranges, however once moved the data source for the charts is still pointing back to the original workbook the sheets were moved from.
For Example
Source Workbook Data Source for Series Values in my Chart ='SourceFile.xlsm'!NamedRange
The named range then refers to '=IF(MOD(COLUMN('Source Data'!$D$5:$O$5),1)=0,'Source Data'!$D$5:$O$5,NA())
The Sheet, 'Source Data' is moved to the new workbook, along with the named ranges, however if I look at the data source for the chart in the new workbook with the original workbook open as well it refers back to the original workbook and if I look at the data source without the original workbook open the data source for the series values still refers back to the original workbook but I get an error message saying, 'Reference is not valid. Reference must be to an open worksheet'
How can I change the data source using vba to point to the moved sheets in the new workbook so that when the new workbook is opened it doesn't refer back to the original workbook?
This is my code as it stands
Many thanks
Bookmarks