Dynamic chart Range... Help!!

[Shawn_archer]

My first post here….I have recorded a macro that could generate a simple trendline chart and I want it make generic so that it can generate charts for a dynamic set of data in a single sheet.
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""Time"""
ActiveChart.SeriesCollection(1).Values = "=Data!$A$2:$A$16"
ActiveChart.SeriesCollection(1).XValues = "=Data!$B$2:$B$16"
Sample data:
Col A ColB
A 10
B 14
C 26
D 27
E 17
F 24
1^st chart should generate Trendline for A,B,C and 2^nd chart should generate D,E,F. My Data type & Columns will remain the same, only that i require that charts ranges are to be dynamic.
Your assistance on this would be a great help for me .. Thanks!!

 

[VoG]

Maybe like this

LR = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""Time"""
ActiveChart.SeriesCollection(1).Values = "=Data!$A$2:$A$" & LR
ActiveChart.SeriesCollection(1).XValues = "=Data!$B$2:$B$" & LR

 

[Shawn_archer]

Hey Thanks for the quick reply.. It create a single chart for the entire data. what i required is N number of chart for N set of data. eg. 2 separate chart for the sample data i have provided earlier..
Is that doable!!

 

[VoG]

Possibly

LR = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR - 2
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "=""Time"""
    ActiveChart.SeriesCollection(1).Values = "=Data!$A$" & i & ":$A$" & i + 2
    ActiveChart.SeriesCollection(1).XValues = "=Data!$B$" & i & ":$B$" & i + 2
Next i

 

[Shawn_archer]

Hey thanks! I'm able to get N number of charts. using the code but it runs every time when i go to that page.. I mean with out running code if i go to the page it runs automatically.. how to stop that

 

[VoG]

Can you post the entire code and where did you put it?

 

[Shawn_archer]

i think when i click on that tab it refreshes the chart that i mistook as it is creating charts again and again sorry.., however but i'm getting more no.of charts than i expect.
I have 31 data rows and 1 label low(i mean column headers) but the code generates 20 carts.

i want 10 data lines per chart so it should give me 3 charts out of the 31 row data.
i.e. 2-11 --- Chart 1
12- 21 ---- chart 2
21 - 31----- chart 3

Here is the code i use...
Sub smpl()

LR = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR - 10
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""Time"""
ActiveChart.SeriesCollection(1).Values = "=Data!$D$" & i & ":$D$" & i + 10
ActiveChart.SeriesCollection(1).XValues = "=Data!$F$" & i & ":$F$" & i + 10
Next i
End Sub

 

[VoG]

Sorry, that's my error. It should be

For i = 2 To LR - 9 Step 10

 

[Shawn_archer]

Kudos!!! Friend.. Its working perfect.. Thanks for all the support!!!.. I'm Just a beginner to macros, it would be really helpful if you direct me to get some basic notes on it...

 

[VoG]

You might find this useful http://www.add-ins.com/vbhelp.htm but if you are interested specifically in charts see http://peltiertech.com/Excel/Charts/chartvba.html and http://www.andypope.info/