Macro to capture data from multiple excel files into another master workbook

[Amaresh Babu K S]

Hi All,

I have a folder which contains more than 300 excel files and each file has data in a sheet named "summary" starting from "a3:bg3". All the files are stored in C:\Documents and Settings\xxx\Desktop\New Folder and each file is named after the person who has taken a test.

I need a vba code to fetch data from the sheet "Summary", from all files, and paste in a master workbook (in the same folder) starting from A2:BG2 and keep on adding in a3, a4. a5..... and continue till all the excel files data has been copied. It will also be helpful if a new folder is created "Completed" and the files moved into this post the data has been pasted in the master workbook.

Note: There might be few blanks in the range and hence the data has to be captured from A3:BG3.

I have tried few codes for this but since am relatively new to VBA am finding it hard to complete

If you can please provide me a code that will do this activity, i'd be glad as i have to submit this by Friday the 7th Dec'12.

Help is appreciated.

Regards,
Amaresh Babu K S

[JBeaucaire]

There are many macros on my site that do things like this.... for instance, in your case:

'WORKBOOKS TO 1 SHEET STACKED
Here's a macro for collecting data from all files in a specific folder.

Workbooks to 1 Sheet

The parts of the code that need to be edited are colored to draw your attention.


You'll notice this macro also moves files to an IMPORTED folder in the same folder as the original files... just as you'd mentioned.

The main "tweaks" you have to make are in the "customize this section" portion:

ORIGINAL

Please Login or Register  to view this content.

You don't need the LR part since you're always taking from the same place. So it would probably change to:

Please Login or Register  to view this content.

[Amaresh Babu K S]

Thank you Jerry. You are a star and you code has helped me finish the job quicker and deliver the project a day prior to the due date.

Thanks a ton.

Regards,
Amaresh babu K S

[JBeaucaire]

Glad to help. Remember to pay it forward!

I have marked this thread solved for you.
In the future please select Thread Tools from the menu above and mark the thread as solved. Thanks.