Get a report from many excel files

Hi,
This is a bit complicated to explain but I’ll do my best. If more info is needed I’ll be more than happy to post it.
I calculate payments for my employees in excel. Every employee has a sheet in a workbook. I have a “Main” sheet with a list of employees. Every employee’s sheet has same fields (also a field with his/her name) and are named: 1, 2 ,3….. I create a new workbook every 2 weeks, or so, by copying the previous workbook to a new one, clean all the previous numbers, put the new numbers and add or replace people in the sheets.
I need a separate workbook to get some graphical overview of the performance of each employee for each activity over time. Now I do this by hand and is very time consuming and I can easily make mistakes.
How to extract the data that I need automatically? Please check the attach to see a demo.
Thank you!