Programmatically transforming Excel sheets is not the way to go. I would review the whole procedure completely and use a database as data source for the reports.
Either let the users enter the source data directly into the database through an appropriate application or import the Excel data into the database (in an automated process).
Once that data is in the database, you can put the data into the right shape using database queries. This is much easier than manipulating cells in an Excel sheet programmatically. Based on these queries you can create reports using a (commercial) report generator. Modern report generators can create reports in various formats including a screen preview, PDF, HTML, RTF and even Excel.
Go away from this Excel bricolage.
A major disadvantage of Excel is that the data has no predefined structure. Users can enter the data in any way they want from highly organized to completely chaotic. This makes it extremely difficult to process it programmatically. A database on the other hand has predefined tables with predefines columns, predefined column types and predefined relations between the tables, which makes it much easier to implement automatisms, because you have a solid base to work on. While Excel sheets can be spread among different file systems and folders and can have random names, a database is usually located at a central and unique place and accessible through a predefined connection string.
Excel sheets may be very sophisticated, but are not scalable. I am developing software professionally. Many of my database projects (including applications as front-end) started with Excel sheets I got from customers. They were very skilled in creating complex sheets. But they reached a point where the sheet became too complex and could hardly evolve any more. The major problem is that complex data sets almost always contain 1-to-n relations and these can only be reproduced very badly in a spreadsheet. A spreadsheet is basically suitable for flat data only. Also, presentation and structure are the same in Excel. But often you need different evaluations and presentations of the same data. Here is where databases (back-end) with an appropriate application (front-end) and report generators come into play.