Combining Multiple WorkBooks into a New Workbook

I posted something similar, but didn't receive any replies, so wonder if my explanation was not very good. I've spend the last few hours searching for a solution to this problem, but to no avail. Many options that are close, but none that match!

Folder 1 has 50 WorkBooks in it, Folder 2 has another 50 WorkBooks in it (which I can put into Folder 1 if that makes things easier). Each Workbook contains a single sheet. I need a macro that will allow me to select two WorkBooks (not all the WorkBooks in the folder) and then combines them into a new WorkBook with two Tabs, maintaining the sheet names from each WorkBook. I need to try and automate the process as I have literally several hundred WorkBooks to combine!

In a perfect world I would like the macro to also be able to automatically save the new file to a user definable folder, giving it filename based on the content of a cell in the new WorkBook. Is this possible to do?

I've looked at lots of examples and also tried the RDBMerge Add in, but none do the above.

If the file saving complicates things, I could do this manually, but I'm try to automate the process as fae as possible owing to the volume of files and the frequency with which this needs to be done.

Many thanks

similar to what I am looking to do:

http://www.excelforum.com/excel-prog...compiling.html

Maybe you could also keep an eye on my post to see if something there might help you out?

Hi apex_predetor1,

Thank you, I'll keep my eyes posted to your thread as well...

Please review the following. GP

Please Login or Register  to view this content.

Hi gporter,

That works really well, but there is one problem, which may be down to me not explaining things very well. Each WorkBook that needs to be merged is a separate file, which currently means I would have to edit the file path for every merge, which would be way too time consuming and isn't practical. Aplogies if my description wasn't clear.

If there is a way to adapt the code to allow the files to be merged to be selected from a dialogue box or userform windowor something, then this would be perfect and really quick. So rather than having the path to the actual file, it is just the path to the folder, which then allows me to select the files visually. The folder path could be hard coded. This could be a window to allow the first file selection, another window to select the second file, or alternatively and propbably better still, just one window where I can Ctrl click both files to be merged (assuming I put all the files to be merged in the same folder) I don't know if that is possible?

Other than that, this is fantastic...

Many thanks

Hi Hangman,

I'm glad that it is satisfying part of your requirement. It is possible to have the user select the files to be merged.

Question: Would you have a list of files to be merged at once or just two at a time only?

GP

Hi gporter1974,

In this instance, the files are located in two different folders, but there is no reason why they can't all be located in the same folder. It is just two files to be merged in all instances, so ideally I would like to be able to Ctrl click the two files to be merged and then click okay and for the rest of the proces to be automatic as per you current code. Does that make sense?

In a perfect world, in would also be great if the files can appear in the merged workbook in the order they are selected, rather than alphabetically, though that isn't a deal breaker, so if it has to be alphabetically then that isn't the end of the world as there may be instances where that is preferred.

Many thanks