Macro to update and populate a summary table with data from multiple and new sheets added.

Hi there!

My conundrum:

There are multiple sheets in a workbook that are all duplicates of one another in terms of layout and design but with varying data between them.

Because these data sheets are the same layout, the specific cell that is required to be represented in the summary sheet's table is always in the same location across all tabs.

I want the macro to be flexible in that it can cope if further data sheets are added, updating the summary table (especially the TOTAL row) and inserting new rows for each of the new added data sheets.

If anyone could offer any guidance it would be greatly appreciated.

Attached is an excel workbook that attempts to resemble my problem and if anyone can provide the macro needed to get this working then I can attempt to transfer that over to my actual spreadsheet I am working on.

Thanks!