Macro to merge multiple sheets into one

Hello Folks

I am in need of a macro to consolidate multiple sheets into one sheet with following conditions,

1) The header labels need to be copied over to the new sheet.
2) All data from individual sheets except the last row (Total Row) needs to be copied over to the newsheet.
3) The last column after transfer of data needs to be populated with the Respective Sheet names.

Attached is an example with requirement described.

Appreciate any help.

Thanks
Kris