Combining 39 files into one

[estillbham]

I have 39 Excel files (one sheet in each) that I want to combine into one large work sheet. All have the same columns. There are no formulas in the sheets.


Is there an easy way to combine all 39 into one sheet in Excel 2007?

 

[Nunes]

hi there!


in the Base workbook, to where you want to copy the 39 files, create a sheet and in column A, starting on cell A1 and ending in cell A39, write the list of the files you want to combine into this base workbook, with FULL PATH AND FULL FILE NAME, something like this:


A1 -> c:documentsmy_folderfile_to_copy_1.xlsx

A2 -> c:documentsmy_folderfile_to_copy_2.xlsx

A3 -> c:documentsmy_folderfile_to_copy_3.xlsx

(...)

A39 -> c:documentsmy_folderfile_to_copy_39.xlsx


(you can use "serverfolderfile.xls" network type paths)

then, and assuming this base workbook where you want to combine is named Base.xlsx, you can use a macro like this:


Sub Macro()


For i = 1 To 39


Workbooks.Open Filename:= cells(i,1)

Sheets(1).Select

Sheets(1).Move After:=Workbooks(Base.xlsx").Sheets(Workbooks("Base.xlsx").Sheets.Count)


Next


End Sub


call the macro having the sheet with the list i told you selected.