Combine multiple workbooks into 1 workbook w/ multiple worksheets

[buffgirl71]

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?

 

[mrice]

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.

 

[ahurst105]

Hey mrice,

Awesome macro! I use it almost everyday! I have one question - is there a way to adjust the code so that sheets get pulled by date? Example - I merge worksheets organized by a date, in this case, every Sunday of every month. So for example I'd be merging files with the dates 1-6-13, 1-13-13, etc. I'd like to be able to run the macro and have the tabs all in date order chronologically starting w/ the earliest date. Currently when I run the macro, regardless of how I have the files arranged in my folder (directory) it pulls them in a random order. I'm not fluent in VBA so i have no idea what kind of code I might need to tell the macro to do this. Can you please help?

Thanks!

Aaron