Generating Reports from a Gradebook

Back in November, the following comment was left for me:

I have a question, though. The current gradebook has one sheet for the dashboard, and it is possible to flick through the various students one by one. What about if I wanted to be able to see the dashboards for many students? I like the idea of having a single dashboard so that I can keep customizing the report as needed, but is there a way of then exporting this to generate 30 individual sheets for the different students in my class? In other words, hit PRINT once instead of 30 times...

Fabulous question, of course. I didn't know the answer to it at the time, but have had it on my "to do" list. Then, Debra Dalgleish shared this fabulous post last week about how to Filter Excel Data on Multiple Sheets. Although the information wasn't exactly what I was needing, it showed me that finding an answer for the commenter was possible. I just had to push a little further. A little Googling led me to this post on Using Excel 2007 for Progress Tracking in the Classroom. Huzzah! The perfect model to poach.

I copied the VBA from the Progress Tracking post into my Beginner's Gradebook, and then changed it only enough to suit my own named cells/ranges. Now, I will tell you that what I know about VBA would fit in a thimble. I'm eager to learn, but my lessons are currently being driven by "What do I need to know right now?" rather than any sort of organized manner. If you have some expertise you are willing to share to tweak the code to make things work more smoothly, I welcome any and all suggestions.

Here's how things are currently set up. In the "Scores" worksheet, I have added a data validation list (in grey box) that has the last names of the students. This cell was assigned a name. There is also a "Run Reports" button associated with the VBA shown below. In the "Report" worksheet, I had to make a couple of changes. First, I removed the data validation list associated with the last name. Why the data validation works in the "Scores" worksheet, but not the "Report" worksheet when the VBA runs is a mystery to me. Beyond that, I had to name the cell in the report with the last name, and use a formula to make it equal to what I named the cell with the new data validation list.

I'll put the VBA  code below, in case anyone has any suggestions. With very few exceptions, it is identical to the one in the Progress Tracking post by Danny Khen---definitely not my work. But for now, feel free to download the workbook and give things a try. It is a macro-enabled workbook, so be sure to tell Excel it's okay to play with. When you click on the "Run Report" button, a new workbook will open and populate with a separate report for every student, all on their own worksheets. All of the data will be in their places with bright shiny faces. It's magic! (Where's Doug Henning when you need him?) All you need to do is tell Excel to print the entire workbook and you're good to go.