I have a very complicated programming/graphing question. Im not sure it is
even possible to do part of what I am trying to accomplish in Excel.

Basically, my question is in three parts: I have a spreadsheet with student
names, exams, scores, grades, and comments. Each student is listed 6-8 times
for the 6-8 different exams.

First - I need to create a separate worksheet for each student with that
students specific information. I have found a macro that I thought would
work for this (from Debra Dalgleishs Contextures web page) but I cant get
it to run. I have virtually NO knowledge of VBA so I have no idea what is
going wrong in the statement. Ive listed the macro below with a note of
where I seem to be running into problems. (BTW €“ I am in office 2007.)

Secondly €“ I need to create a graphic representation of the entire classes
grade percentage for each exam. In essence I need to create a chart that
shows what percentage was pass, high pass, fail for each of the 6-8 exams. I
know how to do that €“ but I also need to copy that chart on to every
students individual worksheet and Im not sure how to do that without having
to manually paste it in to over 175 worksheets.

Finally €“ and this is the part I think may be impossible in Excel €“ I need
to insert an arrow that shows where each student falls on the chart for each
exam. For example, if John Smith scored a pass in Exam A, then on the chart
that shows what percentage of the class scored pass, high pass, fail, etc. in
Exam A, I need to place an arrow pointing at €œpass€� John Smith, on his
particular worksheet. If he scored a high pass on Exam B, the arrow needs to
go over the high pass bar on Exam B. So essentially, I will have a workbook
with a worksheet for each student (around 175). Each worksheet will have that
students exam scores/grades and comments as text. Each worksheet will also
have an identical chart showing the entire class percentage who scored pass,
high pass, honors, etc. for each exam. Each worksheet will have arrows
indicating where the specific student falls on the chart.

How much of this is possible in Excel and any ideas how to do any of it? Or
any ideas of another graphing/charting program I could use where some of this
would be possible?

Here is the macro I am trying to use to do part one. Again, I am woefully
uninformed when it comes to VBA, so please try to be as €œdumbed down€� as
possible in responses. Thanks for ANY help. (Even knowing what I am trying to
do is impossible would be helpful at this point because at least I could stop
trying in this program!)

Sub ExtractStudents()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("ReportsGenerate")
Set rng = Range("Database") ****this is the most recent €œbreak€� in my
running of the macro***

'extract a list of Students
ws1.Columns("C:C").Copy _
Destination:=Range("L1")
ws1.Columns("L:L").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row

'set up Criteria Area
Range("L1").Value = Range("C1").Value

For Each c In Range("J2:J" & r)
'add the student name to the criteria area
ws1.Range("L2").Value = c.Value
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then
Sheets(c.Value).Cells.Clear
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=Sheets(c.Value).Range("A1"), _
Unique:=False
Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
End If
Next
ws1.Select
ws1.Columns("J:L").Delete
End Sub


Thanks again, in advance for any help/advice
- s