Macro for a large report

[Andy0311]

Hello,

I run a report which lists all 31 teachers on my staff and the number of lessons they have graded during a particular grading period. The format looks something like this(see below). What I have to do for each teacher is copy and post their grading information into another report. In the example below, it would be pretty straightforward to select the ranges and copy and paste them. The issue is that the number of lessons isn't two as shown for teacher 1, or one for teacher 2 in the example. Most teachers grade anywhere from 50-400 lessons. The last report contained 6510 rows. That makes selecting the range for eacher and cutting and pasting it involves a lot of scrolling. Is there a way a macro might do this using relative referencing to select the next teacher and run the macro? Is there a better way than a macro? Thanks in advance for your help.

Andy

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Staff[/TD]
[TD]Student[/TD]
[TD]District[/TD]
[TD]Course Name[/TD]
[TD]Lesson[/TD]
[TD]Date[/TD]
[TD]Mark[/TD]
[/TR]
[TR]
[TD]Teacher1 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Joe[/TD]
[TD]GOVS[/TD]
[TD]Health[/TD]
[TD]Allergy[/TD]
[TD]10/22/2019[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Andy[/TD]
[TD]GOVS[/TD]
[TD]PE[/TD]
[TD]Weights[/TD]
[TD]10/23/2019[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Teacher1 Units: 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Teacher2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Liz[/TD]
[TD]GOVS[/TD]
[TD]Algebra[/TD]
[TD]Intro[/TD]
[TD]10/22/2019[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Teacher2 Units:1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jmacleary]

Hello there. Yes, a macro could well do it for you, but why not do a small redisgn of the data and then simply use a filter? You could remove the data currently in column A and instead put the teacher name against each student. Then just filter by the teacher name. Use a count formula to see how many Units there are. You wouldn't need to keep all of one teachers data together either.

 

[Andy0311]

Hello there. Yes, a macro could well do it for you, but why not do a small redisgn of the data and then simply use a filter? You could remove the data currently in column A and instead put the teacher name against each student. Then just filter by the teacher name. Use a count formula to see how many Units there are. You wouldn't need to keep all of one teachers data together either.

Thank you so much. I will try it! I appreciate your help.

A

 

[jmacleary]

You are welcome. If you do decide to go down that route, it would be possible to create a one-off macro that could populate the teachers names for you. If thats something you would like, get back - I'm in the UK so am leaving work soon but can look tomorrow).