Multiple charts in microsoft excel VBA macro

[RossJJ]

Hi,

I'm trying to automatically create a large number of charts, plotting column A vs another column B,C,D etc one at a time. I am however finding A vs. B plots fine but then it plots A vs BC and A vs BCD etc... here is my stolen adjusted code, have tried many variations on the selection used but can't get to grips with it selecting only columns I need. Also if anyone could suggest an automating loop to create the graphs that would be very helpful as I have about 50+ to create:


Sub CreateChart()

Range("a1").Select
Range("a1", Range("a1").End(xlDown)).Select
cola = Selection.Address

Range("b1").Select
Range("b1", Range("b1").End(xlDown)).Select
colb = Selection.Address

Range("c1").Select
Range("c1", Range("c1").End(xlDown)).Select
colc = Selection.Address

mysheetname = ActiveSheet.Name

With ActiveSheet.ChartObjects.Add(125.25, 60, 301.5, 155.25).Select
Application.CutCopyMode = False
ActiveChart.ChartWizard _
Source:=Sheets(mysheetname).Range(cola, colb), _
Gallery:=xlLine, Format:=4, PlotBy:=xlColumns, _
CategoryLabels:=1, SeriesLabels:=1, HasLegend:=1, _
Title:="", CategoryTitle:="", _
ValueTitle:="", ExtraTitle:=""
End With


With ActiveSheet.ChartObjects.Add(125.25, 60, 301.5, 155.25).Select
Application.CutCopyMode = False
ActiveChart.ChartWizard _
Source:=Sheets(mysheetname).Range(cola, colc), _
Gallery:=xlLine, Format:=4, PlotBy:=xlColumns, _
CategoryLabels:=1, SeriesLabels:=1, HasLegend:=1, _
Title:="", CategoryTitle:="", _
ValueTitle:="", ExtraTitle:=""
End With
End Sub

 

[RossJJ]

Oh and also I am using this macro to complete this for multiple datasets hence I haven't selected discrete data ranges (i.e. A1:A500), this I assume would solve the problem normally.