VBA to move generated charts to new sheet

lbradbury

New Member
Joined
May 14, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Capture.JPG
    Capture.JPG
    166.6 KB · Views: 8

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top