Importing specific cells from several workbooks into a new workbook
I have used some of VBA codes for importing data ranges from several workbooks but I failed to do the job I need.
I am using Excel 2010. I have around 2,000 workbooks with three worksheets each. They are identical in their formatting. Some of the cells include text, dates or amounts.
I need to import the content of around 10 cells from each workbook. These cells are not in a range, A5, B6, C10, D12, M24. Couple of them are in ranges; A10-B10 .. etc. Some cells are merged in the source workbooks. I need to import the content of these cells for each workbook in a row in a new worksheet. The end result I want is a row with all data imported from each workbook/worksheet. I would love to have each row stamped with the file name.
Attached a sample workbook template from which I need to import data from all cells marked with XXXX.
Your help is much appreciated.
| Attachment | Size |
|---|---|
| Template.xlsx | 15.9 KB |
It is possible if all files have the same structure
This looks like it was extracted from .doc or .pdf files. I would create a macro to unmerge and re-arrange cells into a common table. Then I would apply a script with that macro to all 2000 files. Maybe Nick have better solution?