Find, Merge and create a new workbook

[jeffwest2]

I have looked around, and while i can find half of a solution i can't find a complete one and hope someone can help.

every week i create from crystal reports a series of reports for each of four areas, these are split into two because of the data that is required, so i have two halfs of a whole.

What i want to do is this:

Afetr exporting the files, Loop through a folder on my network

1. Open up the two similar named files Area1 - 1.xls, Area1 - 2.xls and merge the files together.

2.Save this new file in a predetermined folder with a new name Area1 - 1&2combined.xls

3.Close the files and move them into a 'Done' folder

As i said, i can find some code for merging the files, but these all seem to merge into the open workbook, what i want to do is run this from a form with just a click button to merge the files.

Hope this makes some form of sense.

[davegugg]

How does this work for you:

Please Login or Register  to view this content.

[jeffwest2]

Thanks for the code, just one quick question, the code adds them to two different sheets, how would i change this so they are added just to one sheet?

[davegugg]

I don't understand the question, the makes a workbook with all the sheets from both Area1 - 1.xls and Area1 - 2.xls. Are you wanting the all the sheets to be merged onto one sheet?

[jeffwest2]

Yes, sorry if i did not make that very clear.