Macro to extract values from all sheets after matching the Sheetname and Item Number

Hello AB33,
Thanks for your code but it is not giving the right results as per my OP
I mentioned in my OP that the Item Nos may or may not be in sorted order
I have run your macro and I am attaching the results on a sample data where I have changed the Item No on Sheet3 and colored it for explanation sake.

The code is running only till 3 rows. Please see the Summary sheet after running the code.
Also see that the Item No and the values are not matching (See Sheet3 colored cells)

Kindly amend the code accordingly and also would you please exclude Sheet1 and Sheet2 from your code


Thanks for your time and help
Rashid