Selecting & copying a variable range

Hello

I am having trouble coming up with the proper code to select a variable range.
The situation is that I have courses that have a various numbers of students emailed to me in a select format and I want to be able copy that information into a new sheet and have the information re-organized and pasted into a new master list.

Since I was unable to sort out the variable codes I have been selecting many more cells than necessary with a very messy code!

I have included a modified version of the file that will hopefully make sense

Thanks!