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

[Henk Stander]

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:

Sub AndyPopeMacro()
'
'
    Dim shtSource As Worksheet
    
    Set shtSource = ThisWorkbook.Worksheets("CPI Summary Sheet")
'
    ThisWorkbook.Sheets(Array("CPI Summary Sheet", "CPI Charts")).Copy
    
    shtSource.Range("A1", shtSource.Range("A1").SpecialCells(xlLastCell)).Copy
    ActiveWorkbook.Worksheets("CPI Summary Sheet").Range("A1").PasteSpecial _
                        Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, _
                        SkipBlanks:=False, Transpose:=False
                        
End Sub

Thank you very much