Merge multiple files into one file

[BryanRobson]

Hi. I have had a look online and in this forum and cannot find exactly what I am looking for.

On monthly basis I have 20 XLS files. I have to merge them all manually into one XLS file/workbook (all together on one sheet).

Is there any VBA code which can do this and also remove any blank lines from the final output. The folder each month will change, so I will just place a blank worksheet with the macro in the current folder with the 20 files each month.

Many thanks in advance.

[JEC.]

I can give you a VBA solution but it is easier/more reliable and faster with Power Query. Ever heard of that?

[JEC.]

If the setup of the files are identical you can try to run this macro in an empty sheet (make sure there is something in cell A1 before you run it. A header or something).
It will ask for a folder. Just pick the folder where your files are located.

Please Login or Register  to view this content.

[alansidman]

https://www.youtube.com/watch?v=OFt9b6DgvY0

[BryanRobson]

I can give you a VBA solution but it is easier/more reliable and faster with Power Query. Ever heard of that?

Hi. I have used power query importing one single file and doing some transformations. I didn't know that you could do multiple files at once with power query. I had a look into it then another user posted the video below which helped. that seems to work.

In regards to the code below, I put it in a blank xlsm file except for headers as you stated and saved the macro but it didn't seem to do anything. It ran, but no changes to the file.

[BryanRobson]

https://www.youtube.com/watch?v=OFt9b6DgvY0

Many thanks. I have used power query before but not much. I have imported single files and transformed data a little. I didnt know you could use multiple files and import at once into one file. that worked for me. something new for me. thanks

[JEC.]

Well, the code works fine here. I made small change after I posted it. You could try it again.

[BryanRobson]

Well, the code works fine here. I made small change after I posted it. You could try it again.

Hi. I picked up the amended code and started again. First it didn't work, but I realised that all my files are .XLS. So, I changed one file to .xls and it worked. So, I changed "If it Like "*xls*" Then" to "If it Like "*XLS*" Then" and it all worked. unfortunately, the system the files are extracted from name the files automatically with .XLS extension.

Its really fast your script so that's great. Thanks for your help.

Regards

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[BryanRobson]

Well, the code works fine here. I made small change after I posted it. You could try it again.

Hi. Could you possibly change the code so that I can test if cell A in each row is blank or has the word total in it and if so delete the entire row in both cases?

Thanks in advance.

[JEC.]

Yes sure:

Please Login or Register  to view this content.

[BryanRobson]

Yes sure:

Please Login or Register  to view this content.

Many thanks. perfect