Retrieving data from 400 worksheets and exporting everything in one other worksheet

[maelr]

Hello,

I have an URGENT request.

I am experiencing an issue with retrieving data from multiple excel worksheets (about 400) located in one folder and exporting it in one other excel worksheet.

Each worksheet's structure is the same as the others except for the answers that change from one file to another. I would like to retrieve the data from these 400 worksheets (the ANSWER column for every tab) and export it to OUTPUT.xls having each set of answers in a horizontal display as shown in the output.xls file.

The 400 worksheets follow the same structure than the Example.xls sheet except for the content which changes.

What would you suggest?

Somebody gave me this VBA code but it doesn't seem to work...

Please Login or Register  to view this content.

Thanks a lot,

[arlu1201]

Which part of the code does not work?

If your header is in row1 and your data starts in row2 of the master file, then change this row from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Mike_e]

I had a similar requirement and the fastest solution I found was to use the get external data function. The steps the macro followed were as follows:

1. Run a routine that lists all the files in a directory
2. Run through the list and, using the get external data function, import the data.
3. Copy the data from the file to wherever you want in your workbook
4. Loop the code until all files have been opened.

The time taken to run this for about 50 files was a minute or two. You will need to consider file handling etc as well.