Combining 3 workbooks to one

[Guest]

I have three workbooks, they are titled "APPL CK Today", "APPL CC Today", and
"PPLA CC Today". Each workbook has just a single sheet. I want to combine
those three Workbooks into one WB titled "Recap" with three sheets, the
sheets would be titled "APPL CK Today", "APPL CC Today", and "PPLA CC Today"
and be a copy of the orginal report.

All of the reports are located C:\Documents and
Settings\jouimet\Desktop\Todays Reports

I appreciate your help, thank you.

 

[Guest]

Sub Combinebooks()
Dim sPath as String
Dim bk as Workbook, bk1 as Workbook
Dim bk2 as Workbook

spath = "C:\Documents and Settings\jouimet\Desktop\Todays Reports\"

if dir(sPath & "Recap.xls") <> "" then
kill sPath & "Recap.xls"
End if

set bk = Workbooks.open(spath & "APPL CK Today.xls")
set bk1 = workbooks.Open(sPath & "APPL CC Today.xls")
set bk2 = workbooks.Open(sPath & "PPLA CC Today.xls")
bk1.worksheets(1).copy After:=bk.Worksheets(1)
bk.worksheets(2).name = "APPL CC Today"
bk2.worksheets(1).copy After:=bk.worksheets(2)
bk.Worksheets(2).Name = "PPLA CC Today"
bk.worksheets(1).Name = "APPL CK Today"
bk.SaveAs sPath & "Recap.xls"
bk1.close Savechanges:=False
bk2.Close Savechanges:=False
bk.close Savechanges:=False
End Sub

 

[Otto Moehrbach]

From what you say, this appears to be a one-time thing. Do this. Open all
4 files. Then go into each of the 3 files from which you want to copy and
do Edit - Move Or Copy Sheet, select to make a copy, select the fourth file
as the destination, and click OK. This will put the 3 sheets from the 3
files into the fourth file. Is this what you want to do? Post back if I
missed your intention. HTH Otto

 

[Guest]

Hi Otto,

Actually I need to combine these 3 XLS reports to one workbook so I can
create a daily report each day. I plan on pulling specific data for each
report via a macro and thought it would be much easier if the reports all
resided in the same workbook. Is my thought process wrong?

Thanks
Joyce

 

[Guest]

Thanks Tom, I can see exactly what this code is doing, I appreciate your
help. thanks