0
votes

I have two worksheets. One worksheets with source data and the other sheet ("Correlation Charts") is where I want the chart to be. Below is my code. However, it is still plotting the chart in the source data sheet instead of where I set the range to be.

   Worksheets.Add.Name = "Correlation Charts"

   Set myChart1 = Sheets("Correlation Charts").Range("A3:H16")

   Worksheets("Summary").Activate

    Application.Union(xValue1, yValue1).Select
    With ActiveSheet.Shapes
        .AddChart2(240, xlXYScatter, myChart1.Left, myChart1.Top, myChart1.Width, _
           myChart1.Height).Select
    End With
    With ActiveChart
        .ChartTitle.Text = Range("Correl1_yValue") & " " & "vs." & " " & Range("Correl1_xValue")
    End With
2
@DirkReichel Chart1.Shapes doesn't work, i receive an error. I need to activate Summary sheet to select the two columns of data that I need for the chart.fastfood123
With Sheets("Correlation Charts").Shapes rather than With ActiveSheet.ShapesRory

2 Answers

1
votes

Try to add Worksheets("Correlation Charts").Activate right before the With ActiveSheet.Shapes section.

1
votes

After creating the chart you also could use:

ActiveChart.Location Where:=xlLocationAsNewSheet

to get a new sheet only holding your chart... or if you want it at your already created sheet:

ActiveChart.Location Where:=xlLocationAsObject, Name:="Correlation Charts"

While you also could create the chart anywhere and change the source-data afterwards...