Change number of rows to reflect source data

Hi all,
I would appreciate a bit of help on this as I am struggling to work out an easy way of solving my issue.
The attached workbook shows a cut down version of the real thing, with 2 sheets of data and a summary sheet. The full book has many more sheets, not all of which are used to generate the summary.
I need to be able to link the supplier and associated value to the name of the sheet, hence the set up used, as this allows the user to change the sheet name which is updated in the summary (this data then gets sorted in a pivot table). If there is a better way of doing this, I am happy to learn.
However, my problem is when rows are inserted into the main sheets, the summary doesn't capture these.
I need some code that will check the sheets (but only the ones needed - I can generate a list of these) and return the supplier name and the corresponding total value into the summary sheet, taking in to account all the rows that have data in them.

I hope that all makes sense.
Many thanks,