how to combine excel workbooks into one workbook

Our solution needs to retrieve values from all sheets, plus, sheets may be added or removed at any time. Some contain two, some three, and some up to thirty. Plus, workbooks do not contain the same number of worksheets. In other words, our solution needs to adapt to a changing number of workbooks, and essentially grab values from all workbooks in the folder, however many there are at that moment. And, just to make this a bit trickier, let’s assume that at any time, new workbooks can be added to the folder, or, some workbooks can be removed. We have a folder that contains hundreds of Excel files. Let’s just be crystal clear about our objective. Thanks Regina for your question, and for inspiring this post.

Currently, I copy/paste, but, I want to automate this lookup process. I need to create a single summary sheet that retrieves specific cell values from every sheet in all of these workbooks. For example, some workbooks have two sheets, some have three sheets, and some have up to thirty worksheets. I have a folder with several hundred workbooks, and each workbook may contain any number of worksheets.

Alright my friends, this week I’ll tackle a question I received about retrieving values from workbooks.

How to combine excel workbooks into one workbook