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 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125
| Sub cro()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
verif
organisation_gare
gares_derlig = Cells(65536, 2).End(xlUp).Row
With Sheets("3 - Gares").Range("A1:AU1")
Set c = .Find("CRO")
End With
CRO_gare = c.Column
Range(Cells(1, CRO_gare), Cells(gares_derlig, CRO_gare)).Select
Selection.Copy
Columns(8).Select
Selection.Insert Shift:=xlToRight
Sheets("2 - PAAs").Select
Columns(8).Select
Selection.Insert Shift:=xlToRight
Cells(2, 8).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'3 - Gares'!C[-7]:C[-2],6,0)"
PAA_derlig = Cells(65536, 2).End(xlUp).Row
Selection.AutoFill Destination:=Range(Cells(2, 8), Cells(PAA_derlig, 8))
Worksheets.Add
ActiveSheet.Name = "tempCRO"
Sheets("2 - PAAs").Select
Cells(1, 8) = "CRO"
Range(Cells(1, 8), Cells(PAA_derlig, 8)).Select
Selection.Copy
Sheets("tempCRO").Select
Cells(1, 1).Select
ActiveSheet.Paste
Range(Cells(1, 1), Cells(PAA_derlig, 1)).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.Copy
Worksheets.Add
ActiveSheet.Name = "temp2CRO"
ActiveSheet.Paste
temp2_derlig = Cells(65536, 1).End(xlUp).Row
Range(Cells(2, 1), Cells(temp2_derlig, 1)).Select
che = Application.ActiveWorkbook.Path
cheminCRO = che & "\CRO"
ChDir che
If Dir(cheminCRO, vbDirectory) = "" Then MkDir "CRO"
For Each cell In Selection
Sheets("2 - PAAs").Select
Rows(1).Select
Selection.AutoFilter Field:=8, Criteria1:=cell
Range(Cells(2, 1), Cells(PAA_derlig, 7)).Select
Selection.Copy
Sheets("forme").Select
Cells(2, 1).Select
ActiveSheet.Paste
Sheets("2 - PAAs").Select
Range(Cells(2, 9), Cells(PAA_derlig, 16)).Select
Selection.Copy
Sheets("forme").Select
Cells(2, 8).Select
ActiveSheet.Paste
Sheets("forme").Select
Sheets("forme").Copy
'mise en forme
derlig_forme = Cells(65536, 2).End(xlUp).Row
Range(Cells(2, 1), Cells(derlig_forme, 15)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
If derlig_forme > 2 Then
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Sort Key1:=Range("b2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
Columns("O:O").Select
Selection.NumberFormat = "0#"" ""##"" ""##"" ""##"" ""##"
Columns("A:O").EntireColumn.AutoFit
'fin mise en forme
Cells(1, 1).Select
ActiveWorkbook.SaveAs Filename:= _
cheminCRO & "\" & cell & ".xls"
ActiveWindow.Close
Range(Cells(2, 1), Cells(derlig_forme, 15)).Select
Selection.Delete
Next cell
Worksheets("temp2CRO").Delete
Worksheets("tempCRO").Delete
Sheets("3 - Gares").Select
Columns(8).Delete
Sheets("2 - PAAs").Select
Columns(8).Select
Selection.Delete
Sheets("1 - Choix").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub |
Partager