create multiple x y charts, one for each column with the same x axis using excel-vba

[istvan60]

Hi,

I've been working on the problem of automating the plotting of line diagrams. But I did not yet manage to solve it yet.


I would need multiple charts one for each column (from E to R) using the same column (B) for the X axis. The ultimate aim is to be able to clean out the extreme or outlying values.

Could anyone please help me with this problem, I would really appreciate it.


Thank you!


After recording a macro on one of the worksheets and editing the code I got the following:

I edited it so only the name of the worksheet has to be changed because all the 30 worksheets I have to run this on have the same structure, but the best would be if it could run automatically for all the sheets in the actual file.

Unfortunately it stops after the first graph giving me a runtime error.

Sub plot()
'
' plot Makró
'
' have to change 'st1' to the name of the actual worksheet

Range("B:B,E:E").Select
Range("E1").Activate
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("'st2'!$B:$B,'st2'!$E:$E")
ActiveSheet.Shapes("Diagram 1").IncrementLeft 599.25
ActiveSheet.Shapes("Diagram 1").IncrementTop -70.5

Range("B:B,F:F").Select
Range("F1").Activate
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("'st2'!$B:$B,'st2'!$F:$F")
ActiveSheet.Shapes("Diagram 2").IncrementLeft 601.5
ActiveSheet.Shapes("Diagram 2").IncrementTop 152.25

Range("B:B,G:G").Select
Range("G1").Activate
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("'st2'!$B:$B,'st2'!$G:$G")
ActiveSheet.Shapes("Diagram 3").IncrementLeft 601.5
ActiveSheet.Shapes("Diagram 3").IncrementTop 372.25

Range("B:B,H:H").Select
Range("H1").Activate
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("'st2'!$B:$B,'st2'!$H:$H")
ActiveSheet.Shapes("Diagram 4").IncrementLeft 601.5
ActiveSheet.Shapes("Diagram 4").IncrementTop 592.25

Range("B:B,I:I").Select
Range("I1").Activate
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("'st2'!$B:$B,'st2'!$I:$I")
ActiveSheet.Shapes("Diagram 5").IncrementLeft 601.5
ActiveSheet.Shapes("Diagram 5").IncrementTop 812.25

Range("B:B,J:J").Select
Range("J1").Activate
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("'st2'!$B:$B,'st2'!$J:$J")
ActiveSheet.Shapes("Diagram 6").IncrementLeft 601.5
ActiveSheet.Shapes("Diagram 6").IncrementTop 1032.25

Range("B:B,K:K").Select
Range("K1").Activate
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("'st2'!$B:$B,'st2'!$K:$K")
ActiveSheet.Shapes("Diagram 7").IncrementLeft 601.5
ActiveSheet.Shapes("Diagram 7").IncrementTop 1252.25

Range("B:B,L:L").Select
Range("L1").Activate
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("'st2'!$B:$B,'st2'!$L:$L")
ActiveSheet.Shapes("Diagram 8").IncrementLeft 601.5
ActiveSheet.Shapes("Diagram 8").IncrementTop 1472.25

Range("B:B,M:M").Select
Range("M1").Activate
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("'st2'!$B:$B,'st2'!$M:$M")
ActiveSheet.Shapes("Diagram 9").IncrementLeft 601.5
ActiveSheet.Shapes("Diagram 9").IncrementTop 1692.25

Range("B:B,N:N").Select
Range("N1").Activate
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("'st2'!$B:$B,'st2'!$N:$N")
ActiveSheet.Shapes("Diagram 9").IncrementLeft 601.5
ActiveSheet.Shapes("Diagram 9").IncrementTop 1912.25

Range("B:B,O:O").Select
Range("O1").Activate
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("'st2'!$B:$B,'st2'!O:$O")
ActiveSheet.Shapes("Diagram 10").IncrementLeft 601.5
ActiveSheet.Shapes("Diagram 10").IncrementTop 2132.25

Range("B:B,P:P").Select
Range("P1").Activate
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("'st2'!$B:$B,'st2'!P:$P")
ActiveSheet.Shapes("Diagram 11").IncrementLeft 601.5
ActiveSheet.Shapes("Diagram 11").IncrementTop 2351.25

Range("B:B,Q:Q").Select
Range("Q1").Activate
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("'st2'!$B:$B,'st2'!Q:$Q")
ActiveSheet.Shapes("Diagram 12").IncrementLeft 601.5
ActiveSheet.Shapes("Diagram 12").IncrementTop 2572.25

Range("B:B,R:R").Select
Range("R1").Activate
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("'st2'!$B:$B,'st2'!R:$R")
ActiveSheet.Shapes("Diagram 13").IncrementLeft 601.5
ActiveSheet.Shapes("Diagram 13").IncrementTop 2792.25

End Sub