Copy content of specific cells of multiple workbooks in a single worksheet

[hanh112]

Hello MrExcel expert

I hope you can help me with my problem. I am new to excel and I am not able to solve the problem on my own.

I have multiple excel files in one folder. The files have exactly the same size and format. I would like to summarize the content of all those excel files by copying the content of several cells on each file and combining them into columns in a master sheet.

For example, I need to copy the content of cells B5, B6, and J59 of hundreds of workbooks and place them in three columns A, B, C in a new worksheet.

I search around but couldn't find a suitable VBA code. I also try Query but couldn't figure out how to summarize the data I need.

I appreciate it very much if anyone could help me with this issue.

Thank you

 

[jdellasala]

People vastly underestimate how much learning and work VBA takes, yet always go to it as the first answer. Why is that? Never mind!

This is a job for Power Query. If all your files are in one folder, you can import and merge them all into a single table within a few minutes, and maybe only an hour of YouTube videos.
Mr. Excel has a great playlist cleverly named Power Query, and Mike G from the ExcelIsFun channel has a full blown college course on it - Full Advanced Data Analysis & BI Class (MSPTDA). Power Query, Power Pivot, DAX, M Code, Power BI & Excel which has sample before and after files as well as PDFs. You should learn what you need to know within just a few videos.

Good luck!