VBA to move generated charts to new sheet

[lbradbury]

Hello,

I have an code that generates about 11 distribution charts, the code generates them all to the same location on the data sheet. I want to move the chart to the "Chart Sheet" But each time the macros runs to to generate the distribution charts, the charts are renumbered.

I think I need to have the code name each chart after each chart generations, then code it to move the chart.

But this is a bit over my head.

The code is below of how to generate the charts, it repeats 11 times for each data set.


Sub CreateChart()
Dim Sh As Shape, rng As Range

With ActiveSheet
Set rng = .Range("A3:B" & .Range("A" & .Rows.Count).End(xlUp).Row)
'adjust position and size as needed
Set Sh = .Shapes.AddChart2(Style:=240, XlChartType:=xlXYScatterSmoothNoMarkers, Left:=300, Top:=200, Width:=400, Height:=150)
End With

Sh.Chart.SetSourceData Source:=rng

With Sh.Chart
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Text = "X-Axis"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Text = "Y-Axis"
.ChartTitle.Caption = "Data - Attitude, Cooperation & Core"
.SeriesCollection(1).Name = "Average Distribution"
.HasLegend = True

End With
'Chart for Quality and Quanity
With ActiveSheet
Set rng = .Range("D3:E" & .Range("D" & .Rows.Count).End(xlUp).Row)
'adjust position and size as needed
Set Sh = .Shapes.AddChart2(Style:=240, XlChartType:=xlXYScatterSmoothNoMarkers, Left:=300, Top:=200, Width:=400, Height:=150)
End With

Sh.Chart.SetSourceData Source:=rng

With Sh.Chart
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Text = "X-Axis"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Text = "Y-Axis"
.ChartTitle.Caption = "Quality and Quantity of Work"
.SeriesCollection(1).Name = "Average Distribution"
.HasLegend = True
End With
'Chart for Job Knowledge

 

[lbradbury]

Hi,

Any chance someone could assist me?