VBA Copy paste chart, with independent data reference

gifariz

Board Regular
Joined
May 2, 2021
Messages
82
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

gifariz

Board Regular
Joined
May 2, 2021
Messages
82
Office Version
  1. 365
Platform
  1. Windows
Found out the answer myself :D
VBA Code:
                '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:
VBA 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
 
Upvote 0
Solution

Forum statistics

Threads
1,186,998
Messages
5,961,016
Members
438,514
Latest member
AngSP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top