BASE EMPLOI - DEMO.xls


Re bonjour le Forum,

Je cherche à simplifier ces macros dans le module 2

Qui peut m'aider, svp ?

Bonne aprem.

Seb

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
Sub CONVERTIRFORMATS()
 
    'Convertir en format DATE
Worksheets("BASE EMPLOI").Select
 
 
         Range("T65536").End(xlUp).Select
   Selection.TextToColumns Destination:=Range("T2"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
      Selection.NumberFormat = "dd/mm/yy"
 
        Range("U65536").End(xlUp).Select
   Selection.TextToColumns Destination:=Range("U2"), DataType:=xlFixedWidth, _
     FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
             Selection.NumberFormat = "dd/mm/yy"
 
        Range("AB65536").End(xlUp).Select
   Selection.TextToColumns Destination:=Range("AB2"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
          Selection.NumberFormat = "dd/mm/yy"
 
        Range("AJ65536").End(xlUp).Select
  Selection.TextToColumns Destination:=Range("AJ2"), DataType:=xlFixedWidth, _
   FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
            Selection.NumberFormat = "dd/mm/yy"
 
        Range("AK65536").End(xlUp).Select
Selection.TextToColumns Destination:=Range("AK2"), DataType:=xlFixedWidth, _
 FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
       Selection.NumberFormat = "dd/mm/yy"
 
         Range("AL65536").End(xlUp).Select
Selection.TextToColumns Destination:=Range("AL2"), DataType:=xlFixedWidth, _
      FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
               Selection.NumberFormat = "dd/mm/yy"
 
        Range("AM65536").End(xlUp).Select
Selection.TextToColumns Destination:=Range("AM2"), DataType:=xlFixedWidth, _
      FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
               Selection.NumberFormat = "dd/mm/yy"
 
         Range("AT65536").End(xlUp).Select
   Selection.TextToColumns Destination:=Range("AT2"), DataType:=xlFixedWidth, _
       FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
          Selection.NumberFormat = "dd/mm/yy"
 
      Range("BB65536").End(xlUp).Select
  Selection.TextToColumns Destination:=Range("BB2"), DataType:=xlFixedWidth, _
      FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
           Selection.NumberFormat = "dd/mm/yy"
 
 
 
 
 
 
End Sub

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
Sub CONVERTIRDATES()
Worksheets("BASE EMPLOI").Select
   Range("T2:T1500").Select
    Selection.TextToColumns Destination:=Range("T2"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
 
    Range("U2:U1500").Select
    Selection.TextToColumns Destination:=Range("U2"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
 
    Range("AB2:AB1500").Select
    Selection.TextToColumns Destination:=Range("AB2"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
 
    Range("AJ2:AJ1500").Select
    Selection.TextToColumns Destination:=Range("AJ2"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
 
        Range("AK2:AK1500").Select
    Selection.TextToColumns Destination:=Range("AK2"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
 
         Range("AL2:AL1500").Select
    Selection.TextToColumns Destination:=Range("AL2"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
 
        Range("AM2:AM1500").Select
    Selection.TextToColumns Destination:=Range("AM2"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
 
        Range("AU2:AU1500").Select
    Selection.TextToColumns Destination:=Range("AT2"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
 
         Range("BB2:BB1500").Select
    Selection.TextToColumns Destination:=Range("BA2"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
 
 
End Sub



'======= DEFINIT LA ZONE D'IMPRESSION =============


Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
Sub ZONEIMPRESSION()
Worksheets("BASE EMPLOI").Select
'Détermine la zone d'impression
 
ActiveSheet.PageSetup.PrintArea = Range("A1:BB" & _
Range("A65536").End(xlUp).Row).Address
 
End Sub
'============ CHANGE LA COULEUR DES COLONNES SOMMAIRES =========

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
Sub COULEURCOLONNES()
 
Worksheets("BASE EMPLOI").Select
    ' Trait du bas sur toutes les lignes
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("A2").Select
 
 
    ' Pas de trait sur les colonnes sommaires
 
    Range("F:F").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
 
    Range("M:M").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
 
    Range("S:S").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
 
    Range("Z:Z").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
 
    Range("AE:AE").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
 
    Range("AR:AR").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
 
    Range("A1").Select
 
    ' Copie les couleurs des colonnes
    Range("F3").Select
    Selection.AutoFill Destination:=Range("F3:F1500"), Type:=xlFillDefault
    Range("F3:F1500").Select
 
    Range("M3").Select
    Selection.AutoFill Destination:=Range("M3:M1500"), Type:=xlFillDefault
    Range("M3:M1500").End(xlUp).Select
 
    Range("S3").Select
    Selection.AutoFill Destination:=Range("S3:S1500"), Type:=xlFillDefault
    Range("S3:S1500").End(xlUp).Select
 
    Range("Z3").Select
    Selection.AutoFill Destination:=Range("Z3:Z1500"), Type:=xlFillDefault
    Range("Z3:Z1500").End(xlUp).Select
 
    Range("AE3").Select
    Selection.AutoFill Destination:=Range("AE3:AE1500"), Type:=xlFillDefault
    Range("AE3:AE1500").End(xlUp).Select
 
 
    Range("AR3").Select
    Selection.AutoFill Destination:=Range("AR3:AR1500"), Type:=xlFillDefault
    Range("AR3:AR1500").End(xlUp).Select
 
 
 
    Range("A2").Select
 
End Sub
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
Sub CENTURYGOTHIC8()
Worksheets("BASE EMPLOI").Select
'Met en Century Gothics 8
    Cells.Select
    With Selection.Font
        .Name = "Century Gothic"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .Name = "Century Gothic"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("B2").Select
 
End Sub


Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
Sub MFCATRAITER()
' Mise en forme conditionnelle "A TRAITER"
Worksheets("BASE EMPLOI").Select
    ActiveWindow.SmallScroll Down:=-12
    Range("B65536").End(xlUp).Select
    Selection.FormatConditions.Add Type:=xlTextString, String:="A TRAITER", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 90
        .Gradient.ColorStops.Clear
    End With
    With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(0.5)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
 
 
 
End Sub