to join more workbooks in one

[71marco71]

Dear friends
I have the macro below that works fine but I don’t understand how the
new workbook that will contain all sheets is open (I can see a copy
command but I don’t see a paste command). How can I give a specific
name to the new workbook and save it in a specific forlder? I would
like also close the workbook I had previously open without saving it.
Thank you very much for your help

bkList = Array("Bk1.xls", "Bk2.xls", "Bk3.xls", _
"Bk4.xls", "Bk5.xls", "Bk6.xls", "Bk7.xls")
for i = lbound(bkList) to ubound(bklist)
set wkbk = workbooks.Open(blList(i))
if i = lbound(bkList) then
wkbk.Sheets.copy
set wkbk1 = ActiveWorkbook
else
wkbk.Sheets.Copy After:=wkbk1.Sheets(wkbk1.sheets.count)
end if
Next
' now delete sheets you don't want
wkbk1.Sheets.Select
activewindows.SelectedSheets.Printout

 

[Tom Ogilvy]

the copy command is copying sheets. When you copy sheets and don't specify
a destination, Excel places them in a new workbook which it creates (thus no
paste is required). This is now the activeworkbook.

wkbk1 then holds a reference to this workbook, so if you want to save it
just do

wkbk1.SaveAs Filename:="C:\MyFolders\newname.xls"

bkList = Array("Bk1.xls", "Bk2.xls", "Bk3.xls", _
"Bk4.xls", "Bk5.xls", "Bk6.xls", "Bk7.xls")
for i = lbound(bkList) to ubound(bklist)
set wkbk = workbooks.Open(blList(i))
if i = lbound(bkList) then
wkbk.Sheets.copy
set wkbk1 = ActiveWorkbook
else
wkbk.Sheets.Copy After:=wkbk1.Sheets(wkbk1.sheets.count)
end if
Next
' now delete sheets you don't want
wkbk1.Sheets.Select
activewindows.SelectedSheets.Printout

 

[71marco71]

Hi tom
Now I have understandood how the new workbook has been created but
don’t know where I have to write the macro. I would like to write th
macro in one sheet named “report” and copy to it every month all sheet
contained in the others workbooks I want to join. So, I think, It'
necessary every month to delete all sheets contained in "Report" an
than copy the new sheets. Before printing I would like also to numbe
the pages. I have tried to do this but in vain.
Thanks in advance for your precious hel