more workbooks in one

[71marco71]

I have a macro that join more workbooks in one.
I would like to write this macro in one sheet named “report” and cop
to it every month all sheets contained in the others workbooks I wan
to join. Than I would like to number the pages.
Someone can help me?

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
Nex

 

[Tom Ogilvy]

Dim bkList as Variant
Dim i as Long
Dim sh as Worksheet
Dim sh1 as Worksheet
sh = Thisworkbook.Worksheets("Report")
sh.Cells.Clear
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(bkList(i))
for each sh1 in wkbk.Worksheets
set rng = sh.Cells(rows.count,1).End(xlup)(2)
sh1.UsedRange.copy Destination:=rng
Next
Next