1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
| Sub brut_accachaciacu_vers_washed()
Dim Chemin As String, Fichier As String
Dim Feuilles, Sh As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Feuilles = Array("acc", "ach", "aci", "acu")
Feuilles2 = Array("ACC washed", "ACH washed", "ACI washed", "ACU washed")
Chemin = "C:\DEV\DEV MENSUEL BRUT\"
Fichier = Dir(Chemin & "*.xls")
Do While Fichier <> ""
If Left(Fichier, 7) = "achats " Or Left(Fichier, 4) = "aci " Or Left(Fichier, 4) = "acu " Or _
Left(Fichier, 5) = "jour " Then
Workbooks.Open Chemin & Fichier
Sh = Application.Index(Feuilles2, Application.Match(Left(Fichier, 3), Feuilles, 0))
Sheets("Sheet1").[A1:N2050].Copy
With ThisWorkbook.Sheets(Sh)
.[B1:O2050].PasteSpecial xlPasteValues
End With
ActiveWorkbook.Close False
ElseIf Left(Fichier, 3) = "bal" Then
Workbooks.Open Chemin & Fichier
Sheets("Sheet1").[A1:I2050].Copy
With ThisWorkbook.Sheets("BAL washed")
.[U1:AC2050].PasteSpecial xlPasteValues
End With
ActiveWorkbook.Close False
ElseIf Left(Fichier, 3) = "TVA" Then
Workbooks.Open Chemin & Fichier
Sheets(Sheets.Count).[A1:O250].Copy
With ThisWorkbook.Sheets("VAT washed")
.[A1:O250].PasteSpecial xlPasteValues
End With
ActiveWorkbook.Close False
End If
Fichier = Dir
Loop
ThisWorkbook.Activate
ThisWorkbook.Sheets("PILOTAGE").Select
[A1].Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub |
Partager