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
"John Bundy" wrote:
> 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.
> --
> -John
> Please rate when your question is answered to help us and others know what
> is helpful.
>
>
> "stacyjean622" wrote:
>
> > 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
|
Similar Threads
