Automate Combine 2 Spreadsheets into two worksheets

[Uni]

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

 

[Joel]

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]

Joel;

Thanks so much for your response – here’s the answers to the questions that
you had:

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

2. I’ve created a “staging†area. It would reside on
G:\CIO\Billing\FundSummary\Export

3. & 4. The workbook and associated worksheet names are:

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. I’ve 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, I’m 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]

Below is the macro you requested. I created a new file for the combined
workbook called AllFunds56SummaryAll.xls

Here are the answers to your questions
1) Would I need a separate macro to run all differently named workbooks &
worksheets?

Answer:You can have multiple macros in the same workbook. Each macro will
have a different name.

2) 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, I’m assuming that
it would be best to create a separate worksheet for each combination. Does
that make sense?

What you said make sense. Because Access will over-write the workbooks each
week you can't have the macro in one of the two workbooks that access creates
because you will loose the macro. I suggest that you create a workbook that
is called combine.xls (or something similar) with my macro.


Sub combinebooks()

Const folder = "G:\CIO\Billing\FundSummary\Export\"

Workbooks.Open Filename:= _
folder & "AllFunds56Summary.xls"
'the next instruction will create a newworkbook
ActiveWorkbook.Sheets("ALL").Copy
Set newbook = ActiveWorkbook
Workbooks("AllFunds56Summary.xls").Close
Workbooks.Open Filename:= _
folder & "AllFunds56SummaryRED.xls"
With newbook
'using copy with after will not create new book
'the dot infornt of Sheets will put the worksheet into newbook
ActiveWorkbook.Sheets("ALLRED").Copy after:=.Sheets(1)
'the dot infront of saveas will save the newbook
.SaveAs Filename:=folder & "AllFunds56SummaryAll.xls"
'the dot infront of close will close newbook
.Close
End With
Workbooks("AllFunds56SummaryRed.xls").Close
End Sub

 

[Uni]

Thanks, Joel. I'll try this and get back with you.

Again, many thanks,

Uni

 

[Uni]

Joel;

This worked GREAT!!! Thank you so very much.

You're my hero

Uni