Sub copie()
Dim Classeur1 As String
Dim Onglet As Worksheet
Classeur1 = "Semaine.xlsm"
For Each Onglet In Workbooks(Classeur1).Worksheets
Onglet.Copy before:=Feuil1
Next
End Sub
Sub copie()
Dim Onglet As Worksheet
Dim Classeur As Workbook
For Each Classeur In Application.Workbooks
If InStr(1, Classeur.Name, "agen") > 0 Then
For Each Onglet In Classeur.Worksheets
Onglet.Copy before:=ThisWorkbook.Sheets("Feuil1")
Next Onglet
End If
Next Classeur
End Sub
Sub testBIS()
Application.ScreenUpdating = False
Dim Wbk As Workbook, ws As Worksheet
For Each Wbk In Application.Workbooks
If Wbk.Name Like "agenc*" Then
For Each ws In Wbk.Worksheets
ws.Copy after:=Workbooks("Module.xls").Sheets(Workbooks("Module.xls").Sheets.Count)
Next ws
End If
Next Wbk
End Sub
Bonsoir à tous
Une proposition testée (à partir de ta macro test)
Code:Sub testBIS() Application.ScreenUpdating = False Dim Wbk As Workbook, ws As Worksheet For Each Wbk In Application.Workbooks If Wbk.Name Like "agenc*" Then For Each ws In Wbk.Worksheets ws.Copy after:=Workbooks("Module.xls").Sheets(Workbooks("Module.xls").Sheets.Count) Next ws End If Next Wbk End Sub
Sub testTER()
Dim wbk As Workbook, tWbk As Workbook, ws As Worksheet, flag As Boolean
Set tWbk = ThisWorkbook
For Each wbk In Workbooks
If wbk.Name Like "agenc*" Then flag = True: Exit For
Next wbk
If flag = False Then
MsgBox "Le fichier n'a pas été trouvé!", vbCritical, "Fichier non ouvert"
Exit Sub
Else
For Each ws In wbk.Worksheets
ws.Copy after:=tWbk.Sheets(tWbk.Sheets.Count)
Next ws
End If
End Sub
Option Explicit
Sub test()
Dim wbk As Workbook, tWbk As Workbook, ws As Worksheet, flag As Boolean
Set tWbk = ThisWorkbook
For Each wbk In Workbooks
If wbk.Name Like "agenc*" Then
For Each ws In wbk.Worksheets
ws.Copy after:=tWbk.Sheets(tWbk.Sheets.Count)
Next ws
flag = True
End If
Next wbk
If flag = False Then MsgBox "Le fichier n'a pas été trouvé!", vbCritical, "Fichier non ouvert"
End Sub
If flag = False Then MsgBox "Le fichier n'a pas été trouvé!", vbCritical, "Fichier non ouvert": Exit Sub