Complicated VBA report... help
 

I'm trying to generate a report of student test data that shows student performance broken down into four areas on a single test. Students must take three different tests that each have four reporting areas.

I've been given a report that was generated in the past that has a layout similar to this:

Student Name Test Name Report Area
Sally. Test 1. Area 1.1 Area 1.2 Area 1.3
##. ##. ##
##. ##. ##

Test 2. Area 2.1. Area 2.2. Area 2.3
##. ##. ##

John. Test 1. Area 1.1. Area 1.2. Area 1.3
##. etc.

I have the data set on separate sheets as it is generated by the testing company, but I'm having trouble figuring out how to create an output or sheet that has this type of format using formula, vlookups, queries, vba, etc. Is it possible, without having to manually create these miniature tables for the each student, to show all of their test data for each test, including multiple attempts of the same test using vba and macros?

Thank you for even making it all the way through this post!