Creating many worksheets with text from initial worksheet (and mor

[stacyjean622]

I have a very complicated programming/graphing question. I’m 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
student’s specific information. I have found a macro that I thought would
work for this (from Debra Dalgleish’s Contextures web page) but I can’t get
it to run. I have virtually NO knowledge of VBA so I have no idea what is
going wrong in the statement. I’ve 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
student’s individual worksheet and I’m 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

 

[John Bundy]

The particular line you reference is assuming you have a named range
"Database". All of these things can be done. I took about an hour to build
something similar myself (have a few teacher friends) if you would like to
email me i can send it to you. Maybe it will work how it is or you might have
some insights to make it better. I have it populate a large chart for each
'grade' with the students names along the bottom. Another button creates
sheets for each student that displays their data along with a line chart that
shows their progress on all grades to date.

 

[stacyjean622]

Hi John,

Thanks for the response. I figured my problem in the code was the word
"database" but I can't figure out what word I should be using. (I've tried
using the name of my workbook; the name of the worksheet, etc.) The workbook
that I copied the macro from didn't seem to have anything named database but
it worked there.

I will also email you - I would love to try the workbook you have built and
see if it will work for me. Thanks.

Stacy