Report - Summarising data/figures across multiple worksheets into one

A Client has asked me to compile a report based on our standard bill of quantities we use I already have a macro that get the data from the worksheets that are not named report but puts the data in the wrong row it should be in row 7 not 170.

I also have to summarise the figures typed into the quantity column for the different items in the invoices in to the front report at the same time with an option to refresh as new invoices are created. I have attached my base document with the macro in it so far but my limited ability/knowledge has be stuck here.

Any help would be greatly appreciated.

Dan

Report - Macro.xls

thank you for the response i have added in some data of how the results could look, i have used sum to for the data but this has to be changed for over 100 items every time raise new invoice.

Regards Dan