Creating multiple reports using a master file with data from multiple files

[AnaHL]

I have created a master excel report with 5 main tabs, 3 of which contain multiple pivots that draw their data from 3 separate CSV files. The CSV files do not have a single common denominator and cannot be joined. I need to replicate the master ~500 times for different users. Each user can only see their own info. Any suggestions?
What I have thought of so far is add 4 extra tabs to the main file - 3 containing data for each of the pivot tabs and one I use as control room - due to the number of files I'm breaking them down into smaller groups and using the names from a range with variable size. I am currently trying to write a code that will:

1. save individual copies of the master file
2. filter the CSVs only copy one individual user at a time data into the respective data tab of the respective user report
3. refresh the pivots
4. delete the extra tabs

So far I have only managed to do step 1.
Could you please help me either with steps 2-4 or suggest any alternatives? I'm open to any working automated solution.
Thanks,
Ana

 

[Rijnsent]

Hi Ana,
replicating a file 500 times sounds like a no-go, that's a massive amount of maintenance. What you could do is to build an empty tool and when users open the tool, check out their Windows user name and based on that user name import/link certain CSV files. So you'd need some index file/database where you link the username to the file names.
An alternative that comes to mind: have one master tool and one master template. Use the master tool for your settings and code, and the master template is simply the file you want to create without any data. In your master tool, open the report template, fill it for employee x1, refresh pivots and save it on a location for x1 and close it. Do the same for employee x2 etc.
If you have some code to review/add to, please post it here (anonymized if needed).
Cheers,
Koen