I have a source workbook , call it WorkbookA, with multiple sheets that have chartobjects. I am copying over all the sheets to a WorkbookB. However, on doing so, I find that the charts in the copied over chartobjects in WorkbookB refer to data set in WorkbookA.
How can I change this so that the charts in WorkbookB refer to data sets in WorkbookB?
I tried to change the Formula so that it refers to WorkBookB, but on doing so it gives a Run-Time error '1004' : Application-defined or object-defined error.
After copying over all the sheets from WorkbookA.xlsm to WorkbookB.xls, do the following
Set NewBook = Application.Workbooks.Open("WorkbookB.xls")
For Each sSheet In NewBook.Sheets
For Each chtTemp In .ChartObjects
For Each objSeries In chtTemp.Chart.SeriesCollection
objSeries.Formula = Replace(objSeries.Formula, "WorkbookA.xlsm", "WorkbookB.xls")