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

Staff	Student	District	Course Name	Lesson	Date	Mark
Teacher1
	Joe	GOVS	Health	Allergy	10/22/2019	A
	Andy	GOVS	PE	Weights	10/23/2019	B
Teacher1 Units: 2
Teacher2
	Liz	GOVS	Algebra	Intro	10/22/2019	B
Teacher2 Units:1

<tbody>
</tbody>

 

[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).