Macro to Pull Data from Multiple Workbooks

[jessipeachy]

I have spent two days reading through various forums and trying to work this out on my own. I'm throwing my hands up, right now. This is my dilemma:

I have a quiz that was created in Excel. The quiz is posted in Sharepoint. Participants will open the quiz, save with their name in the file name and then upload their completed assessment to the original Sharepoint folder. I have hidden sheets that automatically grade the quizzes. What I would like to do is create a macro that would pull one specific cell on two different worksheets within each file.

Example:
On "Post Assessment" worksheet, I would like to pull their name (cell A6), on the "Answer Key" worksheet, I would like to pull their score (cell B11).

The format I would like to use for the compiled data should be a simple, two-column worksheet with the participants' name (column A, starting at A2) and corresponding score (column B, starting at B2). That filename is RSC CDA Refresher Post Assessment Results.

I have a workspace created that will sync with Sharepoint so that all completed assessments will automatically be accessible from my computer. The path to the folder that will hold all assessments is:
C:\Users\USER\Desktop\RSC CDA Refresher Post Assessment

I will upload three of the test files that are within that folder.

Ideally, I would like to have a macro that would search the entire folder instead of me having to individually name each file. All assessments will be exactly the same format since users have very limited editing permissions within the file. That brings me to another question... I have the "Answer Key" worksheet very hidden in each assessment. Will that prevent the macro from picking up the score?

Thank you for any help you can give me!