CODEWITHSUNDEEP
excelvbatrendline
Empan
Empan

Reputation: 25

Excel Macro - How to automatically copy a trendline from a graph to a cell

I want to automatically copy a trendline equation from a graph that is being regularly updated to a cell using macros.

I have managed to create a macro that copies the trendline equation to a cell but my issue is that the same trendline equation is always being copied, even when the plot gets updated and the trendline equation changes.

What I have done: while recording a macro, I have copied and pasted the trendline equation into a cell. 

Sub CopyTrendline()
'
' CopyTrendline Macro
'

'
    Range("D33").Select
    ActiveSheet.Paste
End Sub

So, I want to create a macro that copies the current trendline equation from a plot into a cell (after the plot and thus trendline equation has been updated). Right now the same trednline equation is always being copied, even when the plot and trendline equation has been adjusted.

Upvotes: 1

Views: 2917

Answers (1)

Justyna MK
Justyna MK

Reputation: 3563

You can use the following code to retrieve the trendline formula:

Sub GetEquation()
    Dim strFormula As String

    With ActiveSheet
        With .ChartObjects(1).Chart.SeriesCollection(1).Trendlines(1)
            strFormula = .DataLabel.Text
        End With
        .Range("B12").Value = strFormula
    End With
End Sub

Result:

enter image description here

You'd need to include it in your Worksheet_Change event if you want it to automatically update the formula every time you change the values. Hope it helps.

Upvotes: 1

Related Questions