Generate multiple bar charts for each row in data range

[Excelidiot01]

Hi

I've tried a few how-to-write-macro sites to try and automate a graph form data each data row without success.

I'm after a macro to produce a column chart with trend line for each row company - example as per chart 1 in attachment

x axis = month
y axis = data
title = Company name in cell A and PPM DATA 2020

I have managed to find a macro to produce my column graphs ( though without trend line) - the only issue is all graphs are located into the same sheet, I 've tried to modify to change to insert to new sheet for every graph but can't get it to run without error,

I've tried to add new location sheet to below parts of macro but doesn't work so I am doing something wrong but no idea how to fix if any one can help

With ActiveSheet.Shapes.AddChart.Chart

macro that I currently using,


Sub Addcharts()
Dim i As Integer 'rows
Dim j As Integer 'colmns

i = Cells(Rows.Count, 1).End(xlUp).Row

For j = 2 To 13
With ActiveSheet.Shapes.AddChart.Chart
.ChartType = xlColumnClustered
.SeriesCollection.NewSeries
With .SeriesCollection(1)
.Name = "=" & ActiveSheet.Name & "!" & _
Cells(1, j).Address
.XValues = "=" & ActiveSheet.Name & "!" & _
Range(Cells(2, 1), Cells(i, 1)).Address
.Values = "=" & ActiveSheet.Name & "!" & _
Range(Cells(2, j), Cells(i, j)).Address
End With
End With
Next j

End Sub