How to combine multiple excel files in one workbook

[Fanny18]

Hi, how to combine multiple excel files in one workbook?
I have 202 excel files, i need to combine it into 1 workbook.
For eg, each excel files have 12 worksheets (Jan'22, Feb'22,........), i just need to combine all excel files with "Feb'22" sheet into one workbook become 202 worksheets with "Feb'22".

 

[John_w]

Try this macro, changing the path string, containing the 202 workbooks, as needed. It should import every "Feb'22" sheet to the macro workbook to sheets named "Feb'22", "Feb'22 (2)", "Feb'22 (3)", etc.

Public Sub Import_Sheet_From_Workbooks()

    Dim path As String, fileName As String
    Dim wb As Workbook
    
    path = "C:\path\to\workbooks\"  'CHANGE THIS
    
    If Right(path, 1) <> "\" Then path = path & "\"
    
    Application.ScreenUpdating = False
    
    fileName = Dir(path & "*.xls*")
    Do While fileName <> vbNullString
        Set wb = Workbooks.Open(path & fileName, True)
        wb.Worksheets("Feb'22").Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        wb.Close
        fileName = Dir
    Loop
    
    Application.ScreenUpdating = True
    
    MsgBox "Done"
    
End Sub