Appending / Copying worksheets from many excel files into one master file

[AdLib13]

Hey everyone –

I have a folder of ~250 excel files containing similar formatted data tables that I would like to combine / merge / append all into one long master file.

The data tables are on the first worksheet of each file, and occupy the columns A-H. Depending on the file, the data occupies anywhere from the first ~20 rows to the first ~40 rows.

Columns ABC are strings, columns DEF are entered numbers, and columns GH are formulated averages referencing pairs of rows in DEF. Because GH are averages of pairs, there is always half as many rows as A-F.

I have attached a screenshot of one of the files to show what it looks like (also linked here): \1

I would like to copy the data table from each of the files into one master file, making one very “tall” table (the first row of one table would be directly below the last row of the one before it). This means that the data would still occupy only columns A-H, with no empty rows in A-F.

If anyone could tell me how to accomplish this without manually copying each one into the master, it would be much appreciated. I do not have much experience with macros or VBA, but am a quick learner…

Thanks!

- Adam.

[JBeaucaire]

On the sheets you are copying from, is there data to be IGNORED below the tables to be copied? The reason I ask is because the simplest way to spot the lastrow is to jump UP to it from the bottom of column A. But if there is other data in the way, we wouldn't reach the bottom of the data you want copied. See the green coded section in the macro to see what I mean.

So, can we jump up from the bottom of column A on those sheets, or is there other stuff in the way?

I have a standard compilation macro I use for this, you will need to tweak the "strPath" variable for your needs.

Also, it imports EVERY file in the folder, so make sure the ones you don't want are taken out of the folder, at least temporarily.

Please Login or Register  to view this content.

How to use the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

[AdLib13]

wow, JBeaucaire, thanks so much. the data tables are the only things in the worksheet, so i guess the jump up command should work.

I'll try out the code and let you know...

Thanks again!

[AdLib13]

unfortunately, the macro did not work. I followed your instructions, pressed Alt+F8, clicked the macro, and pressed run.

Though it looks like nothing happened, the name of the now-blank worksheet did change to "Report" and all other worksheets were deleted.

I realized that I was running the macro from my new "compilation" worksheet, which happened to be inside that folder... but realized that might cause issues, so i moved it out of the folder and ran it but the results are still the same...

Lemme know how we can make this work, I appreciate you helping me out.

[JBeaucaire]

Post back up the code after you tweaked it. Be sure to wrap it in code tags.

[AdLib13]

Post back up the code after you tweaked it. Be sure to wrap it in code tags.

Here's my modified code, for a folder full of excel files at C:/Compilation

Please Login or Register  to view this content.

I ran the debugger and put a break inside the "do while" loop, but the program never stopped there. So then I put a watch on strFileName and throughout the entire code, it's always an empty string... which is probably why the code never enters the loop. I'm guessing there's something wrong with the file name assignment, but I don't know much about this stuff...

thanks for any help!

[ChemistB]

I think you just need to put a \ at the end of the line where you define

Please Login or Register  to view this content.

Does that work?

[JBeaucaire]

Yep, forgot the closing "\"

[Solarissf]

thanks!!!!! this worked for me also

[chrisveau]

Hi JBeaucaire
i am very interesting in your simple and very straight forward macro !
i have tried it but it recalls always the common first row .
How can i have it only once and then data underneath (no duplication of first commun row - titles)

thanks a lot for your help

[JBeaucaire]

It's normally against the rules to post your own questions in other people's threads, but this seems a useful clarification on the existing macro:

Please Login or Register  to view this content.

I suppose you change that to A2 to skip row1.