excel vba copying chart objects to a new workbook

hi

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
        With sSheet
                For Each chtTemp In .ChartObjects
                    For Each objSeries In chtTemp.Chart.SeriesCollection
                        objSeries.Formula = Replace(objSeries.Formula, "WorkbookA.xlsm", "WorkbookB.xls")
                    Next
                Next
        End With
    Next sSheet
   
    NewBook.Save
    NewBook.Close