Struggling with Excel Macros

In my new position as instructional coach I've been playing even more with spreadsheets than I ever have before.  I love, and I mean LOVE spreadsheets and reports but I've hit a big road bump here and am in need of some help.

In my last post I talked about the campus wide intervention we've been doing during the day that has been really successful.  The only problem is that we can't take attendance for it this year in the program we usually use for attendance so we've been doing it through Google Forms.  It's great and has been perfect since the kids rotate the intervention group they go to every 3 weeks but creating the summary report of who's missing has been the problem.

So the form looks like this on Google Forms:

And when I download the responses from Google to sort them alphabetically and by grade level it looks like this:

I've been spending part of my afternoon every Friday making a sheet for each day of the week, splitting the cells so there is a column for Last name, First Name, ID # and Grade Level, and then combining all the sheets into one as a summary at the end.  So the shots of everything go in this order:

New Sheet for each day

Splitting the Cells:

Putting each student (celebrity) on their own line

Summary Sheet sent to APs

I know there's a way of using Macros to do more of this work for but every time I try to write and run a macro for even splitting the cells my debugger looks like this and I know there's not supposed to be that much yellow on it.

If anyone knows of any tips or tricks for how to make this go more smoothly, how to write a program to automate half of the process or a better way of doing this that would be awesome.  I'm passing this weekly task off to an admin assistant soon and I'm trying to make the process smoother for her than it was for me.  If anyone wants to create something for me using the dummy version that I took screenshots of here the file can be downloaded here.

Thanks a ton and Happy Thursday to everyone!!!