Consolidating multiple files per sheet

[kiki_xy]

Hi everyone,
I would like to create a macro to consolidate multiple files in one masterfile but to collect the information by sheet.
For example
File 1.xls has 2 sheets Austria, Belgium
File 2.xls has 3 sheets UK, Austria, France
File 3.xls has 5 sheets Austria, Belgium, France, Netherlands, Sweden
The masterfile I would like to create sheets per each country and consolidate the information per each Country, all the information from Austria to be copied to Austria, Belgium to Belgium, etc.
I saw various solutions of consolidation of files but no solution per consolidating per sheets.
Thank you in advance.

 

[ashish mehra]

Hi,

Please check the following code:

Sub CombineFiles()
   
    Dim Path            As String
    Dim FileName        As String
    Dim Wkb            As Workbook
    Dim WS              As Worksheet
   
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Path = "C:\" 'Change as needed
    FileName = Dir(Path & "\*.xlsx", vbNormal)
    Do Until FileName = ""
        Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
        For Each WS In Wkb.Worksheets
            WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        Next WS
        Wkb.Close False
        FileName = Dir()
    Loop
    Application.EnableEvents = True
    Application.ScreenUpdating = True
   
End Sub

Regards,
AM

 

[kiki_xy]

Thank you Ashish!

What I see it does this copies the sheets in the new Excel but does not consolidate information from Italy to Italy, what it does it creates another sheet Italy (2), Italy(3) ... and so on for each repeated country.

Now I have it in one file but still in separate tabs. I would like the information to be copy pasted in the continuation of the previous one.