Help with macro to search and record from multiple worksheets

Hi all,

I would really appreciate some help with developing, what I believe should be, a relatively simple solution:

Currently, I'm exporting data from my company dashboard for around 50 projects. The data for each project is populated in individual sheets, all of which have the same structure, just different numbers populating them. What I would like to have is a summary page and a macro that:
1. Writes the name of the worksheet
2. Then searches through the worksheet and records the array A14:A16
3. Then moves onto the next sheet and repeats.

For clarification I've attached an example file which has 3 worksheets: Part X, Part Y, Part Z. The array in each worksheet I would like in my summary page is highlighted in yellow.

Any tips/hints/solutions will be much appreciated!

Thanks,
Chris

Hi guys, thank you all for scripts, they work brilliantly!

Since I download the reports every week or so, rather than copying the macro I was wondering if I could make a master spreadsheet that allows me to type in the file name of the report that I have downloaded, and then runs the macro that sifts through the worksheets as above?