Joel;

Thanks so much for your response €“ heres the answers to the questions that
you had:

1. Since the process over writes the workbooks, Im thinking that I would
be in a completely separate workbook.

2. Ive created a €œstaging€� area. It would reside on
G:\CIO\Billing\FundSummary\Export

3. & 4. The workbook and associated worksheet names a

3. Workbook Name = AllFunds56Summary
4. Worksheet name for this one is ALL

3. Workbook Name = AllFunds56SummaryRED
4. Worksheet name for this one is ALLRED

This is just one example. Ive never coded anything like your example so
please forgive me if I ask ignorant questions.

Would I need a separate macro to run all differently named workbooks &
worksheets? I was €œplaying around€� with this and was still rather puzzled to
where the macro/coding resides. It looks as though you can either €œembed€�
the macro into the workbook or you can use a €œnew€� one. Since the ACCESS
export is throwing these workbooks out in the €œstaging€� directory of €œexport€�
where the previous weeks workbooks would get overwritten, Im assuming that
it would be best to create a separate worksheet for each combination. Does
that make sense?

Again, thanks so much Joel!

/r,

Uni



"Joel" wrote:

It is pretty simple to combine multiple workbooks into a single workbook, but
I need more info

1) Where do you want the macro to run? In one of the workbooks or from a
completely seperate workbook?
2) What directory are the workbooks located?
3) What are the workbook names?
4) What are the worksheet names?

Below is a general; purpose macro that will import all book and sheets into
the workbook where the macro is run

Sub combinebooks()

Const folder = "c:\temp\test"

First = True
Do
If First = True Then
Filename = Dir(folder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" Then
Workbooks.Open folder & "\" & Filename
Set oldbook = ActiveWorkbook
For Each sh In ActiveWorkbook.Sheets
With ThisWorkbook
sh.Copy after:=.Sheets(.Sheets.Count)
End With
Next sh
oldbook.Close
End If
Loop While Filename < ""
End Sub


"Uni" wrote:

Hi - Is there any way to automate using a macro the procedure to combine two
separate workbooks into one workbook where the separate workbooks are now on
separate worksheets? I know how to manually do this but need to automate
this for our operators who will be running this process on a weekly bases.
I'm capturing conditional data using MONARCH, exporting it out to a flat
file, importing flat file into an ACCESS program slices & dices the
information according to the requirements then exports it out into two
separate workbooks. Since I'm trying to avoid these operators from having to
open any files to manually manipulate the data (too time consuming - it
produces over 20 files) I'm looking for other alteratives.
I greatly appreciate any suggests.
Thanks in advance
Uni