get data from different spreadsheets ( in a folder)

Nirvana
Nirvana used Ask the Experts™
on
get data from different spreadsheets ( in a folder) to a single file

I have many files in a folder, I need to copy data and paste it to single workbook. I have worked, I have worked little bit on a macro by googling but needs help

I am attaching the input and output files
sample_-Input.xlsx
sample_output.xlsx.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
the best way to combine workbooks is to make sure that the data is in the same table format. I can't see any point of reference to work with, not even a column to hold the source workbook name.

From what I can see of the source data the code would need to find the data to move which could be slow, then multiply that by the number of files to check.
The workbooks are not required to be open to get the values.
It is much faster to create links to the closed workbooks, and just read the values.

For each workbook in the folder, the values are read to the sheet Input and values transferred to Summary sheet.
If the Summary workbook don't have formulas to calculate, and you don't have any other workbooks open with calculation, I expect you can update from 10 workbook per second.
Calculation can be turned off during update if needed.
sample_output.xlsx.xlsm
Nirvanalearner

Author

Commented:
Dear Ejgil,

The solution is working great. only one change can be great help, from cell "C14" its not picking number if it has currency code. could you please help me with that.
sample_output.xlsx.xlsm
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

The function for C14 reads numbers until a text character (not space) is found.
So if it starts with a text nothing is read.

Can you give an example of what C14 is with currency code.
Nirvanalearner

Author

Commented:
please find the file
sample_-Input.xlsx
Nirvanalearner

Author

Commented:
Sheer Brilliance

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial