Combining Multiple Excel Workbooks Into one

[DJElk]

Is there a way in VBA to select a folder with ~20 different Excel Workbooks. Have a loop perform the same task to each workbook in the folder and combine them into one excel workbook?

As of right now I have this code:

[/FONT]
 
[FONT=Arial]Sub MastercurveCreater()[/FONT]
 
[FONT=Arial]Workbooks.Add[/FONT]
 
[FONT=Arial]ActiveWorkbook.SaveAs "N:\******\ MasterCurve Start\" & "InitialSave" & ".xlsx"[/FONT]
 
[FONT=Arial]SelectData.Show[/FONT]
 
[FONT=Arial]'[/FONT]
[FONT=Arial]' DataSelection Macro[/FONT]
[FONT=Arial]'[/FONT]
[FONT=Arial]Range("K1").Select[/FONT]
[FONT=Arial]Range(Selection, Selection.End(xlDown)).Select[/FONT]
[FONT=Arial]ActiveWindow.SmallScroll Down:=-30[/FONT]
[FONT=Arial]Range("K1:K68,N1").Select[/FONT]
[FONT=Arial]Range("N1").Activate[/FONT]
[FONT=Arial]Range(Selection, Selection.End(xlDown)).Select[/FONT]
[FONT=Arial]Range("K1:K68,N1:P68").Select[/FONT]
[FONT=Arial]Range("N1").Activate[/FONT]
 
[FONT=Arial]Selection.Copy[/FONT]
 
[FONT=Arial]Windows("InitialSave.xlsx").Activate[/FONT]
 
[FONT=Arial]Range("A1").Select[/FONT]
 
[FONT=Arial]ActiveSheet.Paste[/FONT]
 
[FONT=Arial]SaveWorkbook.Show[/FONT]
 
[FONT=Arial]'You can use this to delete all xlsx files in the folder Test[/FONT]
[FONT=Arial]On Error Resume Next[/FONT]
[FONT=Arial]Kill "N:\*******\MasterCurve Start\" & "InitialSave" & ".xlsx"[/FONT]
[FONT=Arial]On Error GoTo 0[/FONT]
 
 
[FONT=Arial]End Sub[/FONT]
 
[FONT=Arial][code\][/FONT]
 
[FONT=Arial]It takes all the data from one spreadsheet to which I specify and copies and paste it into another blank worksheet.[/FONT]
 
[FONT=Arial]I would like to do this for all the worksheets in the folder, but am unsure of how to go about doing it.[/FONT]
 
[FONT=Arial]Any help would be appreciated.[/FONT]
 
[FONT=Arial]Thanks a lot![/FONT]

 

[VoG]

This may help you http://msdn.microsoft.com/en-us/library/cc837974.aspx