Copy Columns from Workbook to MasterWorkbook

Scenario:

I have 300+ workbooks. I need to copy columns C:LastActive (populated) column from the workbooks to a master workbook.

Example:

WB A ----> Copy from column C:LastPopulated to Master WB starting with the first blank column.

So essentially, I have my master WB run the macro select the file and it will copy over. Rinse and repeat.

Where do I start?

Thanks.

Originally Posted by mikeTRON

So I am fairly new with VBA but the following was something I was working on recently for another project, I just modified it to make it work for your needs.

Attachment 290904

There are two macros here. One lists all files in the folder in range C5. So type the address of your folders there then hit LIST FILES. This will populate the file names to be used for the batch processing. I prefer to keep things separated so when things break I can figure out how and why faster.

So once you have the files listed, Type YES in column F next to any files you want to extract data from. This is typically all of them for me, as I typically create a new folder to drop everything in to make sure this is as easy as possible.

Once you have selected yes for them all, hit Process Everything Flagged as Yes. This will loop through all of the files, pulling the data into the Processing tab, then pasting/appending it to right of the data in the DATA tab, like you wanted.

I created a few sample files to grab sample data from and it processed 100 files in 21 seconds so it should do 300 in about a minute.

NOTE: you MUST enable macros, you must have the Microsoft ActiveX Data Objects 2.9 Library checked as an available reference.
To do so use the following: http://msdn.microsoft.com/en-us/libr.../gg264402.aspx

Thank you so much for your help, I believe this is just what I need. However, I do not have "Microsoft ActiveX Data Objects 2.9 Library". Do you (or anyone else) know where I can download this version?

If I run it as is without "Microsoft ActiveX Data Objects 2.9 Library" I get the following error:

Noname.jpg

Is this because I am missing Microsoft ActiveX Data Objects 2.9 Library, or something else?

UPDATE: I am having errors because I am trying to process pre-2010 files (XLS). This code works great if you it is an XLSX file. Do you have any tips to for this to work in Copy/Paste of XLS files (Multiple) to XLSX (Master WORKBOOK)?

Thanks

Originally Posted by xlbiznes

can you post a screen shot of the my settings tab.

Noname.jpg

For some reason it keeps bringing in Col A and only processing the first file....

Originally Posted by xlbiznes

try replacing this line of code to transpose the data

from :

 wrk.Range("a" & lrow).PasteSpecial xlPasteAll

to:

wrk.Range("a" & lrow).PasteSpecial Paste:=xlPasteAll, Transpose:=True

to copy the range that you have mentioned

change this line of code from :

  ActiveSheet.UsedRange.Copy

to:

 ActiveSheet.range("b5:iv53").Copy

That still didn't work.

Here is what my files look like, but much much much longer.

Noname.jpg

Originally Posted by xlbiznes

can you post 2 of your data file (sample with dummy values if information is sensitive).

also post a third file with the output required.

Ok here are 2 sample files and what I would like the final file to look like:

Collection.zip

Couldn't add atachment via this forum so I used a 3rd party, please let me know if you prefer a different host.

THANK YOU SO MUCH!!!!

Originally Posted by xlbiznes

try this file and let me know if it works. tested it on the 2 files and it works as desired.

Wow. THANK YOU SO MUCH! That works just how I wanted it. You have done more than enough and have truly gone out of your way to help me.

But... =) When you were looking at the files, you saw columns that contained nothing but 0. Any idea on how best to clean that up?

You don't have to help any more if you don't want as you have truly exceeded my expectations.

THANK YOU!

Originally Posted by xlbiznes

What we can try to do is the delete all the columns that are starting with 0 on row 5 . Will this logic hold good ?

I am not gonna sleep till i get this thread solved.

That sounds good to me. Logic is good to go.

Originally Posted by xlbiznes

done, let me know if its fine.

Out of curiosity , i would like to know how many files would be merged in a real life scenario ?

That was perfect! Thank you, again, so very much.

On my site there are about 350+ files. With all the other sites combined it is something like 1200+.

If we ran out of room I would just create a 2nd master file. I have to add some more code to it to get it across to our oracle system, but that is a cake walk compared to what you brought to the table.