Copy & Paste specific cells to a summary sheet

Hello Excel wizards,

Hoping to receive some help today on an excel issue I'm facing.

My excel workbook contains 2 worksheets: one named 'data' and the other 'summary'. I'm attaching here for everyone to see.

Before I explain the issue I'm wondering if there is a non-VBA/non-macro approach to solving this issue. If not then i'm okay with any VBA suggestions.

My Data sheet contains rows and ranges that are sectionalized by province and the range will be dynamic. I will continue to insert new rows of data for every month going forward.

What I would like the formula/macro to do is look at the the province, then search for the last month/row that contains numbered data and paste it over to the Summary sheet.

I don't believe a simple v-lookup or index match function could work but I may be wrong. Any help is always appreciated and many thanks in advance!

Thanks for the quick reply FDibbins and great solution!

The preferable method would be for the dataset to have a dynamic range rather than a static one. However not sure if this is possible with using only formulae, and as you suggested a fixed range with absolute references could work fine.

What I see is that the dataset will grow and more locations may be added to the data sheet as I receive more data.

Within each 'province' data range there will be new rows/months inserted which means the dataset will grow.