Multiple excel data loading to seperate workbook

Hi,

I have some basic excel understanding of using functions. Today came across a situation where I have 10 different excel sheets stored at a shared location. All sheet has same format and size of data.
I needed to open one excel sheet copy its selective data from and paste it to another workbook say 'consolidated' which is also stored at shared location.
Then open second sheet from stored location copy selected data and paste it to 'consolidated' as second set of record.

Then open third and so on.

Its manageable upto certain exten but number of sheets are going to increase.

I was looking for some time saving way of doing this. May be a copy button to the first excel clicking loads data into 'consolidated' then I open another click on copy which will move data to 'consolidated'.

Data is like

Excel 1

A1-Part Number B1-10
A2-Subpart 1 B2-6
A3 Subpart 2 B3-8
A4 Subpart 3 B4 -10

Sheet 2

A1-Part Number B1-20
A2-Subpart 1 B2-10
A3 Subpart 2 B3-12
A4 Subpart 3 B4 -14

Consolidated workbook
Part Number 10
Subpart 1 6
Subpart 2 8
Subpart 3 10
Part Number 20
Subpart 1 10
Subpart 2 12
Subpart 3 14

Actually I have to open all the sheets and then click copy which will move data to consolidated workbook..
Attaching sample.
Please advise. Many thx in advance.