# Excel VBA macro - multiple charts



## wootoo (Oct 11, 2007)

Hi There

Quite new to this. I am creating a macro to use create multiple charts. The idea being that I use column A everytime as the X axis and then columns B, C, D etc as the Y axis. Each graph will only have one series, i.e. chart 1 uses AB, chart 2 uses A and C, chart 3 uses A and D etc etc. I am attempting to use dymanic named ranges. My code is almost there however I have two problems; the first graph is produced fine. The second graph however includes colum B as a second (unwanted) series, the next graph includes columns B and C, etc etc. Why is this as they are not included within the named ranges? Secondly, the first graph comes out with a chart title fine however the subsequent graphs do not. Below is my code...

Sub charts()

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

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

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

Range("d1").Select
Range(Selection, Selection.End(xlDown)).Select
mrtest = Selection.Address


With ActiveSheet.ChartObjects.Add(Left:=100, Width:=327, Top:=75, Height:=229)

.Chart.SetSourceData Source:=Sheets("Sheet1").Range(myrangeAREA, myrangeRWE)
.Chart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="custom1"
End With

With ActiveSheet.ChartObjects.Add(Left:=150, Width:=327, Top:=125, Height:=229)

.Chart.SetSourceData Source:=Sheets("Sheet1").Range(myrangeAREA, mrRWECHA)
.Chart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="custom1"
End With

With ActiveSheet.ChartObjects.Add(Left:=200, Width:=327, Top:=175, Height:=229)

.Chart.SetSourceData Source:=Sheets("Sheet1").Range(myrangeAREA, mrtest)
.Chart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="custom1"
End With

End Sub


Any comments greatly appreciated. Also if there is a clever way of looping through instead of repeating the code for each chart that would be most appreciated too.

Many thanks
Sam


----------



## Rollin_Again (Sep 4, 2003)

Can you post your workbook?

Regards,
Rollin


----------



## wootoo (Oct 11, 2007)

I was just using a test sheet with 4 columns and 5 rows of data (including top header row). I have since found a solution using the Union function. 

Cheers
Sam

(keep on rollin'!)


----------

