Sub Bouton2_Cliquer()
Dim wbDest As Workbook, wbSource As Workbook
Dim wsDest As Worksheet, wsSource As Worksheet
Dim myFile As String, myPath As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wbDest = ThisWorkbook
Set wsDest = wbDest.Worksheets(1)
myPath = "D:\Dossier Dest\"
myFile = Dir(myPath & "*.csv")
Do While myFile <> ""
Set wbSource = Workbooks.Open(myPath & myFile)
Set wsSource = wbSource.Worksheets(Split(myFile, ".")(0))
wsSource.Unprotect
wsSource.Cells(1).CurrentRegion.Copy
wsSource.Range(Cells(1, 1), Cells(24, 24)).Copy
With wbDest
.Worksheets.Add After:=Worksheets(Worksheets.Count)
If IsError(Evaluate("=" & wsSource.Name & "!A1")) Then
' si la feuille n'existe pas
.ActiveSheet.Name = wsSource.Name
Else
.ActiveSheet.Name = wsSource.Name
End If
.Activate
.ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
End With
wbSource.Close
myFile = Dir
Loop
Set wsDest = Nothing: Set wsSource = Nothing
Set wbDest = Nothing: Set wbSource = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub