VBA Copy paste chart, with independent data reference

[gifariz]

Hi all. I need to make around 1000 charts (line & scatter plot type), so obviously needs macro.
I have made data can be changed by only changing one cell.
So I need to make VBA that works like this:
1. Change cell B2. All X & Y data needed for the chart will be updated, chart is also updated.
2. Copy paste chart to other cell (after finding cell location by Match function).
3. Repeat.

The problem, the charts that have been pasted are still referring to same data reference that keep being updated.
Any suggestion to make the chart data independent after being pasted?
How to break link chart data?

This is my code now:

Sub PlotAll()

    Dim Sects() As tSection
    Dim SectsID As New Collection
    Dim Num As tNumber
    Dim SheetID As tSheetID
    Dim Setting As tSetting
    Dim i As Long, j As Long, r As Long, c As Long, k  As Long
    Dim firstCol As Long, colStep As Long, colNum As Long
    Dim res As Variant
  
    Dim sht As Worksheet
    Dim cht As ChartObject
  
    Worksheets("Analysis").Activate
  
    'Get data
        S111_Init_SheetID SheetID
        S120_Get_Setting Setting, SheetID
        S122_Get_Section Sects, SectsID, Num, SheetID
  
    'Delete all chart in plot
    Worksheets("Plot").Activate
    i = ActiveSheet.ChartObjects.Count
    If i > 0 Then
        For Each cht In ActiveSheet.ChartObjects
            cht.Delete
        Next cht
    End If
  
    firstCol = 3
    colStep = 2
    colNum = 3
    Worksheets("Plot").Activate
    'Iterate
    For i = 0 To 3
  
        'Change a cell value
        Range("BE6").Value = Sects(i).Name
        DoEvents
      
        'Copy chart
        For Each cht In Worksheets("Analysis").ChartObjects
            cht.Copy
        Next cht
      
        'Find location to paste chart & paste chart
        For j = 0 To colNum - 1
            res = Application.Match(Sects(i).Name, Columns(j * colStep + firstCol), 0)
            If Not IsError(res) Then
                c = j * colStep + firstCol
                r = res
                Cells(r - 1, c - 1).Select
                ActiveSheet.Paste
            End If
        Next j
      
    Next i
  
End Sub

Thank you in advance

 

[gifariz]

Found out the answer myself :D

                'After paste the chart
                For Each srs In ActiveChart.SeriesCollection
                    val = srs.XValues
                    val2 = srs.Values
                    srs.XValues = val
                    srs.Values = val2
                Next

In full code:

Sub S162_PlotAll()

    Dim Sects() As tSection
    Dim SectsID As New Collection
    Dim Num As tNumber
    Dim SheetID As tSheetID
    Dim Setting As tSetting
    Dim i As Long, j As Long, r As Long, c As Long, k  As Long
    Dim firstCol As Long, colStep As Long, colNum As Long
    Dim res, val, val2
    
    Dim sht As Worksheet
    Dim cht As ChartObject
    Dim srs As Series
    
    Worksheets("Analysis").Activate
    
    'Get data
        S111_Init_SheetID SheetID
        S120_Get_Setting Setting, SheetID
        S122_Get_Section Sects, SectsID, Num, SheetID
    
    'Delete all chart in plot
    Worksheets("Plot").Activate
    i = ActiveSheet.ChartObjects.Count
    If i > 0 Then
        For Each cht In ActiveSheet.ChartObjects
            cht.Delete
        Next cht
    End If
    
    firstCol = 3
    colStep = 2
    colNum = 3
    Worksheets("Plot").Activate
    'Iterate
    For i = 0 To Num.Sect - 1
    
        'Change a cell value
        Worksheets("Analysis").Cells(6, 57).Value = Sects(i).Name
        DoEvents
        
        'Find location to paste chart & paste chart
        For j = 0 To colNum - 1
            res = Application.Match(Sects(i).Name, Columns(j * colStep + firstCol), 0)
            If Not IsError(res) Then
                c = j * colStep + firstCol
                r = res
                Cells(r - 1, c - 1).Select
                For Each cht In Worksheets("Analysis").ChartObjects
                    cht.Copy
                Next cht
                ActiveSheet.Paste
                For Each srs In ActiveChart.SeriesCollection
                    val = srs.XValues
                    val2 = srs.Values
                    srs.XValues = val
                    srs.Values = val2
                Next
            End If
        Next j
        
    Next i
    
End Sub