If you need workbooks per manager, i.e. 40 managers in your case, there is no automated way.
This would be the macro logic.
You would have to pull each manager's name and create a distinct workbook.
And then do a vlookup for each manager's direct and reporting data
And then create a pivot table for this data in the new workbook.
And then save.
This has to be iterated in a memory loop for all the managers