Find, Merge and create a new workbook

**jeffwest2** · 10-20-2009, 10:55 AM

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** · 10-20-2009, 11:34 AM

How does this work for you:

Public Sub jeffwest2()

Dim Area1Path As String
Dim Area2Path As String
Dim DonePath As String

Area1Path = "" 'Put the path of Area1 - 1.xls between the quotes. Example C:\Program Files\
Area2Path = "" 'Put the path of Area1 - 2.xls between the quotes. Example C:\Program Files\
DonePath = "" 'Put the path of the Done folder here. Example C:\Program Files\Done\

Workbooks.Open Area1Path & "Area1 - 1.xls"
Workbooks.Open Area2Path & "Area1 - 2.xls"

For a = 1 To Worksheets.Count
    ActiveWorkbook.Worksheets(1).Copy After:=Workbooks("Area1 - 1.xls").Worksheets(1)
    Workbooks("Area1 - 2.xls").Activate
Next a

Workbooks("Area1 - 1.xls").Activate
ActiveWorkbook.SaveAs DonePath & "Area1 - 1&2combined.xls"

Workbooks("Area1 - 1&2combined.xls").Close savechanges = False
Workbooks("Area1 - 2.xls").Close savechanges = False

End Sub

**jeffwest2** · 10-21-2009, 10:14 AM

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** · 10-21-2009, 02:35 PM

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?