Creating Multiple Graphs in Excel

On the graph that shows all lines, you will end up with 50 lines ploted on the chart! Do you think that this will anyway clear to read?!?

Here's the code to do that. I've posted a sample sheet for your to test it.

jppinto

Option Explicit
Private Sub chartmaker(ByVal rng_chart As Range)
Charts.Add after:=Sheets(Sheets.Count)
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=rng_chart, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "chart1"
    .Axes(xlCategory, xlPrimary).HasTitle = False
    .Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

Sub CreateCharts()
Dim rng_names As Range
Dim rng_data As Range
Dim rng_chart As Range
Dim i As Long

With Sheets("Sheet1")
    Set rng_names = Range(.Cells(1, 1), .Cells(.Cells.Rows.Count, 1).End(xlUp))
    For i = 2 To .Cells(1, "IV").End(xlToLeft).Column
        Set rng_data = Range(.Cells(1, i), .Cells(.Cells.Rows.Count, i).End(xlUp))
        Set rng_chart = Union(rng_names, rng_data)
        Call chartmaker(rng_chart)
    Next i

End With

End Sub

Select allOpen in new window

CreateMultipleCharts.xlsm

Yes putting 50 lines on one graph would be a mess.  I will actually not do that but I might want to combine three or four graphs so I as just trying to get a process that I could modify that way.

Thanks for the code but that gave me separate sheets for each test with each kid on the horizontal axis.  I want separate graphs for each student with all tests on each graph.  I will try to modify myself but if it is not too much trouble feel free to send it along.

Thanks a ton for all of your help!

ssaqibh, it looks like part of the sub is missing?

Yes you are right. But then how did you accept it?

Sub Macro1()
Set sh = ActiveSheet
For i = 2 To 8
    sh.Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$H$1,Sheet1!$A$" & i & ":$H$" & i)
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveSheet.Name = sh.Cells(i, 1)
Next i
    sh.Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$H$50")
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveSheet.Name = "All combined"
    ActiveChart.PlotBy = xlRows
End Sub

Select allOpen in new window

jppinto, I muat have my rows and columns mixed up because I get just the opposite.  My students are the rows and my test scores are the columns.

ssaqibh, I get an "Object does not support this method." error....

That's what you get when you accept a "solution" without checking it out properly and without exploring all the solutions... good luck!

I guess so.  Thanks for all your help...

BobbaBuoy, is your problem solved or do you still need help?

jppinto did a very nice job of giving me a solution but there was a little mis-understanding that caused some confusion between rows and columns.  His solution, which was a very nice one, gave me one spread sheet for each test with all kids on it.  I wanted one spread sheet for each kid with all tests on it.  I may be able to do some shifting of code to get this to work...haven't had the time to try yet...but I accepted the solution prematurely.

So, no, the problem is not solved.  Thanks for asking!

When you get the

"Object does not support this method." error....

can you tell me which line gives this error by clicking the "Debug" button?

I don't know how your data is layed out but if it's like on the file I've posted, you should get a sheet for each student with the results of all tests. If you data is not on the same layout as the posted file, you just need to Transpose your data, switching columns for rows and rows for columns.

What I did was transpose the rows and columns and then I got exactly what I wanted ippinto.  Thanks a ton everyone!!!!!

If I provided (also) a solution, shouldn't I get also an "Assisted answer" here?

jppinto, unless mine works you get full points

Still waiting for feeback...

I assume that no one is waiting for me from a feedback standpoint.  ippinto your solution was awesome once I transposed rows and columns in my spreadsheet.  Thanks~

So if mine does not work and you are no more interested in getting it to work the it would be appropriate to reopen the question and award points to jppinto. If you are interested in getting my solution to work the tell me more about the error and I shall try to help.

Saqib

You can try this modification, You will not have to transpose your data

Saqib

Sub Macro1()
Set sh = ActiveSheet
For i = 2 To 51
    sh.Activate
    Charts.Add
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$H$1,Sheet1!$A$" & i & ":$H$" & i)
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveSheet.Name = sh.Cells(i, 1)
Next i
    sh.Activate
    Charts.Add
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$H$50")
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveSheet.Name = "All combined"
    ActiveChart.PlotBy = xlRows
End Sub

Select allOpen in new window

@BobbaBuoy: Yes, I'm waiting for feedback from you! If you say that my solution works and ssaqibh didn't work, I think that you should have accepted my solution, instead of one that doesn't work! You should re-open the question and accept the correct one, OK?

How do I re-open it?

At the bottom right of your question there is a link which says "Request Attention"

Click on it and post a request to reopen the question.

I really want to thank iipinto and ssagibh for their assistance in this.  They both gave me quality solutions that worked very well.  I also want to apologize for any "issues" I created by prematurely accepting a solution.

Thanks to both for their "expert" assistance!

No wories...thanks!

You have again done it wrong. This is not about providing assistance. This is about solutions.

You have accepted a solution which does not work. If, at all, you should have selected #35711426 which is supposed to work.

Saqib

To me, it is about providing assistance via solutions to problems I am experiencing.  I pay a monthly fee for this service and, I believe, am free to use it in this manner.  There are usually more than one way to solve a problem (ie: more than one effective solution).  It is an immense help to me to see different approaches.  I saw two very effective solutions and rewarded them both.

Yes but the one you selected does not work. It has its first two lines missing. The one I posted last works and should have been accepted. Let this one go but keep it in mind for future questions.