Creating summary sheet from circa 100 tabs/worksheets - see attached example

Hi,
I have several tabs, 100 or so, and would like to have cost per West, South region etc onto a summary sheet. The summary must separate these costs per individual company per tab/worksheet. I'm looking for a formula, a macro or both to execute this work for me. The tab list grows every time i.e. new ones are set up all the time so the formula has to take this into consideration.
Thanks in advance for the help

Thanks Martin for your help. It has worked perfectly

I am no expert at VBA so i tried quite a simple approach using "Lookup and Reference" formula called VLookup. Check it if it helps.