get data from different spreadsheets ( in a folder)

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
NirvanalearnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
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.
0
Ejgil HedegaardCommented:
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
0
NirvanalearnerAuthor 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
0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Ejgil HedegaardCommented:
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.
0
NirvanalearnerAuthor Commented:
please find the file
sample_-Input.xlsx
0
Ejgil HedegaardCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NirvanalearnerAuthor Commented:
Sheer Brilliance
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.