Macro to create a report file from several other source files

[Elnino1981]

Hi,
I'm relatively new to macros. I am trying to speed up a process by automating it saving me having to copy and paste manually lots of times. What I require is;

I have 10 sales offices and 5 different products. I currently update 5 separate spreadsheets (one for each product) each month and using pivot tables I have 10 tabs (one for each sales office) which I refresh to provide the monthly trend. Currently I then manually create 10 separate spreadsheets (one for each sales office) each of which contains 5 tabs of information (one for each of the pivot tables). I manually copy from the 5 product spreadsheets and paste into the 10 sales offices spreadsheets. Its the copying and pasting that takes the time and what I want to automate.

What I would like to do is;

Create a macro in each of my 5 product spreadsheets, that will take the 10 tabs and copy them into the 10 individual spreadsheets. As I go through each of the 5 product spreadsheets, the macro will then copy the data and paste into a new tab in the 10 sales office spreadsheets. So at the end of the 5th product I will then have 10 spreadsheets created, each containing 5 tabs showing the pivot table summary of each of the products. For eaxample for the Birmingham sales office the final spreadsheet would contain 5 tabs of information (Product A, B,C,D,E) I would need each tab to be named as to what product they are to make the report clear.

As I will do this every month, I think I need the macro to look up the name of the saved 10 sales office files in a cell reference rather than me hard coding it in a macro, as each month the 10 sales office spreadsheets will have a new period number. For example Birmingham sales office - Jan22 then the next month would be Birmingham sales office - Feb22. I want to keep each month's report as a record.

Any help would be greatly appreciated

Cheers
Ed