Combine multiple workbooks into 1 workbook w/ multiple worksheets
 

I have multiple workbooks that I want to combine into one workbook,
putting each of the original workbooks into a new worksheet. Is there
some way I can do that automatically, without cutting and pasting each
workbook?

Combine multiple workbooks into 1 workbook w/ multiple worksheets
 

Yes there is.

Put this macro on a macro module sheet in a new workbook.
Put all the workbooks that you want to combine into the same directory
Change the path in the macro to the one that you are using
Run the macro

Sub GetSheets()
Path = "C:\documents and settings\Martin\my documents\"
Filename = Dir(Path & "*.xls")

Do While Filename < ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub


This will add all the sheets to the new workbook.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=541686