Create multiple project reports using a template and email out to each project manager

[RLA123]

I'm wondering if anyone is able to help. I don't even know if this is possible

I have a master report template (excel sheet name "Report"). Using this template, we want to create a report for each project that is running, which breaks down income/expenditure into different categories and shows variance to budget. Each project has a unique alpha-numerical code.

There is a data tab, in which each month we will ctrl paste in the financial transactions relating to the projects. ("data")

At the moment I have a number of tabs set up, relating to approx 20 project codes and I have a macro which saves a pdf of each tab when I hit the macro button.

We send these reports out monthly, but if there is any change to the report layout then I'd have to update each tab - which will be very time consuming as we are about to create 100+ new projects.

Is there a way that a macro can create a new tab for each project code, automatically PDF the report, save it and email it out to the budget holder? I have a tab with a table showing each project code, the relevant budget holder and their email address within the file (excel sheet name "code list")?

Is this even possible? Even if they are done as separate steps it would be a massive help just to be able to click a button to generate 100 reports each month!

Any help is gratefully received! Thanks! If I haven't explained very well then I'm happy to elaborate on anything.

[Mumps1]

It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific
cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.

[RLA123]

Thank you! I will do so and upload a file