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.