Chart Sheet Source data does not change when sheets are copied to new workbook

Required: The source data of the graphs, on the chart sheet of the newly created workbook, must automatically be updated to the source data in the newly created workbook.

I have 2 sheets, one containing the source data for the graphs on a chart sheet, and a chart sheet containing the graphs.

I need to copy these 2 sheets to a new workbook, where all formulas will be converted to values and very importantly, the source data for the graphs on the chart sheet of the new workbook must be updated to the data source in the new workbook.

Andy Pope provided me with a macro to achieve this, unfortunately when I checked the sources for the graphs on the chart sheet of the new workbook, it still referred to the data in the original workbook. I have attached 2 files indicating the original workbook and the resulting workbook created after I ran the macro below:

Please Login or Register  to view this content.

Thank you very much