Import data from closed workbooks

I have a workbook called "Adherence Marco" that has all my agents on it under worksheet("ART Yearly Numbers"). My agents save their weekly sheet as their name in a folder on the server. I would like a macro that will open each workbook which will be named for the agent (example: Eva Yates) & count the numbers from from Column F and put into workbook("Adherence Macro").Sheets("ART Yearly Numbers") after it looks up the name in column A and puts it in the corresponding row if they did the Kill captain (CC), AR (AR), Review (AR - Review), Reworked (Reworked Tickets), Container Moves (Container Moves), Triage Inbox (Triage Inbox). The weekly log sheet named as the agents name (Eva Yates) will change the date based off the week, so my macro would pull there data and enter it into the sheet "ART Yearly Numbers" as that is for the entire year and will not change. On Sunday, weather the agents workbook is open or closed (ex. Eva Yates), I would like for the sheet to be reset for the new week with all the data already pulled over the the adherence macro sheet!

Any help would be greatly appreciated & I have attached both same sheets for your reference - Thank you in advance for any help!