VBA macro to copy from various sheet to a new file

[PeroPuri]

Hi everyone,
I'm currently dealing with a work which I hope to make it easier using a macro. Right now I found myself with this kind of data:26095
What I'd like to do is to end with something like this:
26097.
Is it possible? Can somebody help me or direct me some site where I can learn to do so?
Thanks everyone in advance and sorry for my english.

[werafa]

this does look complex - I think it would take me a few days to work it out.

some questions:
can you transform the table into the correct layout using a pivot table?
Does powerpivot allow you to combine the tables in the required way? (ive no experience with this - so only have a vague idea of how Powerpivots work)

Can you create a worksheet that tranforms the data into the required layout using formulae? you might be able to copy and paste your input data into this worksheet, and copy the tranformed data into your final table.
you could use the macro recorder to record the steps and automate parts of this