Copy rows from several sheets and compile into list on another sheet

I am not good enough to do this on my own and you folks have always helped me out in the past.

I attach a demo workbook to try and show my problem.
I have a workbook with 9 sheets of data on them with a final 10th sheet where I wish to combine all this lovely info.

The data on each separate sheet is for different suppliers and is likely to grow so I cant just copy and paste the info across.

Is there a way for Excel to automatically on opening/closing (pref closing as that will be when I have amended the data) to search for rows where there are entries and then copy those rows only onto the 'Overview' sheet. I would prefer the data to have no blank rows and to have the first sheets data at the top of the list on so on down.

Can it be done?

I would also appreciate, if it can be done, if you would explain your solution so I might learn how you did it.

Many thanks,

Andrew