Hi All,
I am really struggling with a macro to create a chart from a data range and then resize and reposition chart.
First problem is the Chart plots 2 Series when all I want is to plot one series based on the XY data and add a trendline
Next it renames the Chart to some incremental number so when I re-run the macro it does not recognise the Chart.
I have tried to name the Chart using ActiveChart.Name = "Chart 1" but is not working.
Anyhow maybe if I show you code and data range it might make more sense. Any help appreciated
<table border="0" cellpadding="0" cellspacing="0" width="192"><col style="width:48pt" span="3" width="64"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;width:48pt" height="17" width="64">Pressure</td> <td class="xl65" style="width:48pt" width="64">
</td> <td class="xl65" style="width:48pt" width="64">Flow</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" align="right" height="17">48.875</td> <td class="xl65">
</td> <td class="xl65" align="right">712.0475</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" align="right" height="17">54.675</td> <td class="xl65">
</td> <td class="xl65" align="right">766.501</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" align="right" height="17">56.875</td> <td class="xl65">
</td> <td class="xl65" align="right">786.6311</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" align="right" height="17">59.175</td> <td class="xl65">
</td> <td class="xl65" align="right">807.3928</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" align="right" height="17">61.975</td> <td class="xl65">
</td> <td class="xl65" align="right">832.2982</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" align="right" height="17">65.175</td> <td class="xl65">
</td> <td class="xl65" align="right">860.2939</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" align="right" height="17">69.175</td> <td class="xl65">
</td> <td class="xl65" align="right">894.6346</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" align="right" height="17">66.675</td> <td class="xl65">
</td> <td class="xl65" align="right">873.2541</td> </tr> </tbody></table>
Sub Macro6()
'
' Macro6 Macro
'
'
Range("P37:Q44").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Data'!$P$37:$Q$44")
ActiveChart.ChartType = xlXYScatter
ActiveChart.SeriesCollection(1).Select
ActiveSheet.ChartObjects("Chart 25").Activate
ActiveChart.SeriesCollection(1).Trendlines.Add
ActiveSheet.ChartObjects("Chart 25").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).Select
Selection.Forward = 5
Selection.Backward = 20
ActiveSheet.ChartObjects("Chart 25").Activate
ActiveChart.Legend.Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 25").Activate
ActiveSheet.ChartObjects("Chart 25").Activate
Range("L66").Select
End Sub
Chart should look like this with macro but it doesnt
-- removed inline image ---
I am really struggling with a macro to create a chart from a data range and then resize and reposition chart.
First problem is the Chart plots 2 Series when all I want is to plot one series based on the XY data and add a trendline
Next it renames the Chart to some incremental number so when I re-run the macro it does not recognise the Chart.
I have tried to name the Chart using ActiveChart.Name = "Chart 1" but is not working.
Anyhow maybe if I show you code and data range it might make more sense. Any help appreciated
<table border="0" cellpadding="0" cellspacing="0" width="192"><col style="width:48pt" span="3" width="64"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;width:48pt" height="17" width="64">Pressure</td> <td class="xl65" style="width:48pt" width="64">
</td> <td class="xl65" style="width:48pt" width="64">Flow</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" align="right" height="17">48.875</td> <td class="xl65">
</td> <td class="xl65" align="right">712.0475</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" align="right" height="17">54.675</td> <td class="xl65">
</td> <td class="xl65" align="right">766.501</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" align="right" height="17">56.875</td> <td class="xl65">
</td> <td class="xl65" align="right">786.6311</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" align="right" height="17">59.175</td> <td class="xl65">
</td> <td class="xl65" align="right">807.3928</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" align="right" height="17">61.975</td> <td class="xl65">
</td> <td class="xl65" align="right">832.2982</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" align="right" height="17">65.175</td> <td class="xl65">
</td> <td class="xl65" align="right">860.2939</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" align="right" height="17">69.175</td> <td class="xl65">
</td> <td class="xl65" align="right">894.6346</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" align="right" height="17">66.675</td> <td class="xl65">
</td> <td class="xl65" align="right">873.2541</td> </tr> </tbody></table>
Sub Macro6()
'
' Macro6 Macro
'
'
Range("P37:Q44").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Data'!$P$37:$Q$44")
ActiveChart.ChartType = xlXYScatter
ActiveChart.SeriesCollection(1).Select
ActiveSheet.ChartObjects("Chart 25").Activate
ActiveChart.SeriesCollection(1).Trendlines.Add
ActiveSheet.ChartObjects("Chart 25").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).Select
Selection.Forward = 5
Selection.Backward = 20
ActiveSheet.ChartObjects("Chart 25").Activate
ActiveChart.Legend.Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 25").Activate
ActiveSheet.ChartObjects("Chart 25").Activate
Range("L66").Select
End Sub
Chart should look like this with macro but it doesnt
-- removed inline image ---