Almost 6,000 lines ... I need help to loop or optimize

I've been dragging this bit of code for years and always meant to tighten it up, but never got to it or could sort out how.

This is for a gradebook, the code copies each students' data from the other sheets to a "grade report" that can be printed out. Mostly, it's just 30 macros which increment by 1, but now I need to expand that to 40 students and don't want to see this file get fatter with this stuff.

Each macro is executed by a button ... the full workbook is attached (with identifying stuff scrubbed but zipped because all those lines ... )

Please Login or Register  to view this content.

Originally Posted by Bernie Deitrick

Replace all your code in Module 2 with the code in the second code block, where your last macro uses an argument and a variable i. The last macro is called by the same Subs you had, but they just pass the parameter you need to the new version. Creating the other 30 is left as an exercise for you I hope you see the pattern. I'm not sure what you workflow is but you could also do all 40 at once using a macro like this - if you print or save the report, that could be in the loop, too.

Please Login or Register  to view this content.

Bernie Deitrick,

Your solution works very well, except for the snip above. Even futzing with the math a bit, I can't get the two rows of Homework to land correctly. The dummy scores count up from 1.00 and each button number should correspond with the value of first column of homework, e.g. student 04 would get 4.00, 4.00; student 12 would get 12.00, 12.00; etc. That explanation is probably not as clear as it could be. You can see this though if you play around with the buttons a little in the first workbook and compare to this one (with your suggestions).

Attachment 381620

Norie,

Thanks for the tip! (There are a lot of other places to tighten things in this workbook )

Originally Posted by davesexcel

Checkout your example and the YouTube video on how to make it work, I didn't finish it the code for you, but you will easily see what you need to do to finish it.


Attachment 381626

Attachment 381627

davesexcel,

Thanks for the video. I really like this idea as 40 students would be better accessed like this, but I can't figure out what that thing is or how to make it from scratch . I mean, it's not a combo box or a list box, is it?

Originally Posted by Bernie Deitrick

Ooops. Sorry about that. Too little coffee in my blood when I replied:

Your original was

Worksheets("Homework").Range("F5:U6").Copy

Change this

Worksheets("Homework").Range("F" & I * 4 - 1 & ":U" & I * 4).Copy

to this

Worksheets("Homework").Range("F" & I * 2 + 3 & ":U" & I * 2 + 4).Copy

Bernie Deitrick,

This works perfectly. Don't blame the coffee though; the coffee is innocent