You have two options:
Either you do it manually or you somehow automate the process with VBA.
In the latter case you would somehow select the cells to move to the
consolidation sheet and VBA would add the source sheet name next to the
transferred data.

HTH
Kostis Vezerides