Creating Multiple Graphs in Excel

I have a spread sheet with 50 rows (one for each student) and 7 columns (one for each test).  I want to create a separate graph of this data for each student (and I don't want to do it one at a time) and I want one graph which shows all lines.  Any help?
Bob SchneiderCo-OwnerAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Try this macro
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

Open in new window

0
 
jppintoCommented:
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?!?
0
 
jppintoCommented:
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

Open in new window

CreateMultipleCharts.xlsm
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Bob SchneiderCo-OwnerAuthor Commented:
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!

0
 
Bob SchneiderCo-OwnerAuthor Commented:
ssaqibh, it looks like part of the sub is missing?
0
 
Saqib Husain, SyedEngineerCommented:
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

Open in new window

0
 
jppintoConnect With a Mentor Commented:
"I want separate graphs for each student with all tests on each graph."

My code creates a chart for each student with all tests results! Please check the attached image!

It's my opinion that the points are not well assigned because my solution does what you wanted! Do you agree?

jppinto
Capture.JPG
0
 
Bob SchneiderCo-OwnerAuthor Commented:
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....
0
 
jppintoCommented:
That's what you get when you accept a "solution" without checking it out properly and without exploring all the solutions... good luck!
0
 
Bob SchneiderCo-OwnerAuthor Commented:
I guess so.  Thanks for all your help...
0
 
Saqib Husain, SyedEngineerCommented:
BobbaBuoy, is your problem solved or do you still need help?
0
 
Bob SchneiderCo-OwnerAuthor Commented:
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!
0
 
Saqib Husain, SyedEngineerCommented:
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?
0
 
jppintoCommented:
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.
0
 
Bob SchneiderCo-OwnerAuthor Commented:
What I did was transpose the rows and columns and then I got exactly what I wanted ippinto.  Thanks a ton everyone!!!!!
0
 
jppintoCommented:
If I provided (also) a solution, shouldn't I get also an "Assisted answer" here?
0
 
Saqib Husain, SyedEngineerCommented:
jppinto, unless mine works you get full points
0
 
jppintoCommented:
Still waiting for feeback...
0
 
Bob SchneiderCo-OwnerAuthor Commented:
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~
0
 
Saqib Husain, SyedEngineerCommented:
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
0
 
Saqib Husain, SyedEngineerCommented:
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

Open in new window

0
 
jppintoCommented:
@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?
0
 
Bob SchneiderCo-OwnerAuthor Commented:
How do I re-open it?
0
 
Saqib Husain, SyedEngineerCommented:
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.
0
 
Bob SchneiderCo-OwnerAuthor Commented:
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!
0
 
jppintoCommented:
No wories...thanks!
0
 
Saqib Husain, SyedEngineerCommented:
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
0
 
Bob SchneiderCo-OwnerAuthor Commented:
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.
0
 
Saqib Husain, SyedEngineerCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.