Select all files in a folder automatically

minhhai91 

Here you go:

Sub Fusionner()
    Dim wbFusion As Workbook
    Dim wbCible As Workbook
    Dim shCible As Worksheet
    Dim stDossier As String
    Dim stFichier As String
    
    With Application.FileDialog(4) ' msoFileDialogFolderPicker
        .InitialFileName = ThisWorkbook.Path
        If .Show Then
            stDossier = .SelectedItems(1) & Application.PathSeparator
        Else
            Beep
            Exit Sub
        End If
    End With
    
    Set wbFusion = ThisWorkbook
    
    stFichier = Dir(stDossier & "*.xls*")
    Do While stFichier <> ""
        Set wbCible = Workbooks.Open(stDossier & stFichier)
        For Each shCible In wbCible.Worksheets
            shCible.Copy After:=wbFusion.Worksheets(wbFusion.Worksheets.Count)
        Next shCible
        wbCible.Close SaveChanges:=False
        stFichier = Dir
    Loop
End Sub