https://support.office.com/en-us/art...1-E9FC8ADEEEB5

You could use your Master workbook with the nice sheets to create a PivotTable that creates data "Connections" which can be the employee workbooks. Since they are all laid out the same, once they are connections, the PT can be set to refresh when open, and used as a consolidated data table for your nice sheets that are easy to read or ready for reports. If you are creating the employee workbooks, you can add the workbook as a connection to your master workbook at that time. If you don't create the employee workbooks, you could view the folder where they are saved before you need to run your report, and add connections for the ones that are new since you last checked. That would be the one manual step in the process.

Once the workbooks are all connected, if they are changed by the employees, that new data will feed into your PivotTable.

It seems that the employee sheets were created for visual impact versus table-oriented. They are nice looking, but the layout makes for difficult reading for PivotTable purposes. I don't know how many employee workbooks there are, but you could create an additional sheet on each workbook that is laid out row-table style for easy export to PivotTable which cell formulas that reference the cells on the nice pages that employees enter info into.

When you make connections in the PivotTable setup process, you are asked to pick a workbook, and then a sheet in that workbook...that is a single "connection."