Combine 50 excel file into one new file

[tajulit]

Hi All,

I have 50 Excel file in One folder. Every file has same column such A-E but rows are different. Some have 1000 rows data & another may more or less rows.
I want combine all files data into one file in same folder.


Thanks in advance

[JOHN H. DAVIS]

Here's one I use. Hope it will be useful too you as well.

Sub tajulit()
'I create a workbook called Combined
'I open all the workbooks I want to combine in the order I want, in your case all 50
Dim wbk As Workbook, wbkC As Workbook
Set wbkC = Workbooks("Combined.xlsx")
For Each wbk In Workbooks
    If wbk.Name <> wbkC.Name Then
        With wbk
            .Sheets("Sheet1").UsedRange.Offset(1).Copy wbkC.Sheets("Sheet1").Range("A" & Rows.Count).End(3)(2)
        End With
    End If
Next wbk
End Sub

[tajulit]

Here's one I use. Hope it will be useful too you as well.

Sub tajulit()
'I create a workbook called Combined
'I open all the workbooks I want to combine in the order I want, in your case all 50
Dim wbk As Workbook, wbkC As Workbook
Set wbkC = Workbooks("Combined.xlsx")
For Each wbk In Workbooks
    If wbk.Name <> wbkC.Name Then
        With wbk
            .Sheets("Sheet1").UsedRange.Offset(1).Copy wbkC.Sheets("Sheet1").Range("A" & Rows.Count).End(3)(2)
        End With
    End If
Next wbk
End Sub

Hi JOHN H. DAVIS,
Thanks for your code. Its great. I am learning VBA.
Is there any way complete this task without opening excel files myself?

[JOHN H. DAVIS]

Try:

Sub tajulitz()
Dim CurFile As String, DirLoc As String
Dim DestWb As Workbook
Dim ws As Worksheet

DirLoc = ThisWorkbook.Path & "\tst\" 'Enter your file folder location here
CurFile = Dir(DirLoc & "*.xlsx")

Application.ScreenUpdating = False
Application.EnableEvents = False

Set DestWb = Workbooks("Combined.xlsx")

Do While CurFile <> vbNullString
    Dim OrigWb As Workbook
    Set OrigWb = Workbooks.Open(filename:=DirLoc & CurFile, ReadOnly:=True)
    Set ws = OrigWb.Sheets("Sheet1")

        ws.UsedRange.Offset(1).Copy DestWb.Sheets("Sheet1").Range("A" & Rows.Count).End(3)(2)
   
    OrigWb.Close SaveChanges:=False
    CurFile = Dir
Loop


Application.ScreenUpdating = True
Application.EnableEvents = True

Set DestWb = Nothing

End Sub