How to combine multiple excel file in one worksheet

[sourabh24u]

Hi Team,

I have more than 5 excel workbook with same Row and it need to combine in one excel sheet,
Could you please help us how it will be combine in one excel sheet,

[Alf]

Perhaps a code like this could be of help?

The first thing you need to change is the sPath, for testing purposes I extracted your zip file to the folder "a_test on the C drive so that's why I use the sPath = "C:\a_test\" and you must change it to the proper path where your files are stored.

The sPath will import all files in the sPath folder if they have the ".xlsx" extension. From the first file the macro finds it will copy the headings but skip it of the rest of the files.

As I've used "ThisWorkbook" the name of the workbook that contains this code does not mater as "ThisWorbook" will be taken as the workbook that contains this code. So one need not specify the name of the macro containing workbook.

Option Explicit

Sub ProcessAllFiles()

    Dim sPath As String
    Dim Wb As Workbook
    Dim sFile As String
    Dim i As Integer
    
    i = 1
    
    sPath = "C:\a_test\"
     
    sFile = Dir(sPath & "*.xlsx")
    
    Application.ScreenUpdating = False

    Do While sFile <> ""
         
        Set Wb = Workbooks.Open(sPath & sFile)
    If i = 1 Then
        ActiveSheet.UsedRange.Copy
        ThisWorkbook.Activate
        Range("A1").PasteSpecial xlPasteAll
        Wb.Close SaveChanges:=False
        i = i + 1
    Else
        ActiveSheet.UsedRange.Offset(1).Copy
        ThisWorkbook.Activate
        Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteAll
        Wb.Close SaveChanges:=False
        i = i + 1
    End If
    
    Application.CutCopyMode = False
         
        sFile = Dir
        
    Loop
        
    ActiveSheet.Columns.AutoFit
   
   Application.ScreenUpdating = True
    
End Sub

Alf

[sourabh24u]

y path is C:\Users\Arpit\Desktop\New folder

[sourabh24u]

Hi thankyou for reply

but it is not working

[Alf]

Did you define the sPath properly?

sPath = "C:\Users\Arpit\Desktop\New folder\"

If you did't add the last "\" then the macro will not work.

Alf

[sourabh24u]

it is working now thank you very much

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.