Hi everyone!!

I have 100+ excel workbooks in a folder with multiple sheets in each workbook. I am in need for a vb macro code that would search/loop through all the workbooks in a specified sheet and return a specified cell value.

Ex:
I have workbooks like apple, banana, cocoa, mango, orange. Each of these work book have a sheet named sales and in that sheet [sales] there is a cell "m6" and i need that value in a separate workbook where i want to run this code. Along with value from "m6" i need the name of the workbook from where that value is extracted.

Data should look like
s no sheet name value

additionally:
If i can add hyperlink to the value, it would be easier and wiser to open if i needed to make any correction.

Honestly, i tried google but to no avail. I couldn't find any code tailored to my needs. Hence, i turned towards this.

Thanks in advance.