Macro trop longue

M

matoupitou

XLDnaute Occasionnel
BONJOUR
PAS CRIER et rester cool
j'ai une macro TROP LONGUE car erreur de compilation
je ne sais ni où la couper ni comment écrire des "boucles?"

qq1 peut-il m'aider? (de vous à moi) je préfèrerai qu'elle reste en 1 MACRO

MES REMERCIEMENTS A L'AVANCE
:confused::

Sub bw_vers_excel()
'
' bw_vers_excel Macro
' mise en forme suite à l'export ajout des entente lots + ventes + stocks
'
' Touche de raccourci du clavier: Ctrl+w
'
Cells.Select
Selection.ClearFormats
With Selection.Font
.Name = "Segoe UI"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Rows("4:4").Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=15
Columns("AA:AB").Select
Range("AB1").Activate
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("C:C").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("B:B").Select
Selection.Cut
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Columns("E:G").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("H:I").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=8
Columns("N:N").Select
Selection.Cut
Columns("P:p").Select
Selection.Insert Shift:=xlToRight
Columns("O:Q").Select
Selection.Cut
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=13
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("G2").Select
ActiveCell.FormulaR1C1 = "REF13 - UPC"
Range("I2").Select
ActiveCell.FormulaR1C1 = "1ère com"
Range("L2").Select
ActiveCell.FormulaR1C1 = "Désignation"
Columns("S:T").Select
Selection.Cut
Columns("R:R").Select
Selection.Insert Shift:=xlToRight
Columns("R:T").Select
Selection.Cut
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=12
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("G2").Select
ActiveCell.FormulaR1C1 = "NUM1"
Range("H2").Select
ActiveCell.FormulaR1C1 = "NUM2"
Range("I2").Select
ActiveCell.FormulaR1C1 = "GENRE"
Columns("P:p").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("P2").Select
ActiveCell.FormulaR1C1 = "RAFRAICH"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "VTE ORIGINE (mep)"
Range("R2").Select
ActiveCell.FormulaR1C1 = "LOTS?"
Range("R3").Select
ActiveWindow.SmallScroll ToRight:=15
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 231
ActiveWindow.ScrollRow = 273
ActiveWindow.ScrollRow = 321
ActiveWindow.ScrollRow = 370
ActiveWindow.ScrollRow = 421
ActiveWindow.ScrollRow = 476
ActiveWindow.ScrollRow = 531
ActiveWindow.ScrollRow = 606
ActiveWindow.ScrollRow = 667
ActiveWindow.ScrollRow = 748
ActiveWindow.ScrollRow = 816
ActiveWindow.ScrollRow = 877
ActiveWindow.ScrollRow = 942
ActiveWindow.ScrollRow = 1013
ActiveWindow.ScrollRow = 1088
ActiveWindow.ScrollRow = 1149
ActiveWindow.ScrollRow = 1214
ActiveWindow.ScrollRow = 1278
ActiveWindow.ScrollRow = 1350
ActiveWindow.ScrollRow = 1414
ActiveWindow.ScrollRow = 1495
ActiveWindow.ScrollRow = 1586
ActiveWindow.ScrollRow = 1666
ActiveWindow.ScrollRow = 1757
ActiveWindow.ScrollRow = 1838
ActiveWindow.ScrollRow = 1919
ActiveWindow.ScrollRow = 1993
ActiveWindow.ScrollRow = 2064
ActiveWindow.ScrollRow = 2116
ActiveWindow.ScrollRow = 2190
ActiveWindow.ScrollRow = 2252
ActiveWindow.ScrollRow = 2255
ActiveWindow.ScrollRow = 2245
ActiveWindow.ScrollRow = 2226
ActiveWindow.ScrollRow = 2216
ActiveWindow.ScrollRow = 2203
ActiveWindow.ScrollRow = 2194
ActiveWindow.ScrollRow = 2184
ActiveWindow.ScrollRow = 2177
ActiveWindow.ScrollRow = 2171
ActiveWindow.ScrollRow = 2164
ActiveWindow.ScrollRow = 2158
ActiveWindow.ScrollRow = 2155
ActiveWindow.ScrollRow = 2152
ActiveWindow.ScrollRow = 2145
ActiveWindow.ScrollRow = 2142
ActiveWindow.ScrollRow = 2135
ActiveWindow.ScrollRow = 2129
ActiveWindow.ScrollRow = 2119
ActiveWindow.ScrollRow = 2109
ActiveWindow.ScrollRow = 2103
ActiveWindow.ScrollRow = 2097
ActiveWindow.ScrollRow = 2093
ActiveWindow.ScrollRow = 2090
ActiveWindow.ScrollRow = 2087
ActiveWindow.ScrollRow = 2084
ActiveWindow.ScrollRow = 2077
ActiveWindow.ScrollRow = 2074
ActiveWindow.ScrollRow = 2067
ActiveWindow.ScrollRow = 2064
ActiveWindow.ScrollRow = 2058
ActiveWindow.ScrollRow = 2045
ActiveWindow.ScrollRow = 2035
ActiveWindow.ScrollRow = 2025
ActiveWindow.ScrollRow = 2009
ActiveWindow.ScrollRow = 2000
ActiveWindow.ScrollRow = 1983
ActiveWindow.ScrollRow = 1967
ActiveWindow.ScrollRow = 1957
ActiveWindow.ScrollRow = 1948
ActiveWindow.ScrollRow = 1941
ActiveWindow.ScrollRow = 1935
ActiveWindow.ScrollRow = 1932
ActiveWindow.ScrollRow = 1928
ActiveWindow.ScrollRow = 1925
ActiveWindow.ScrollRow = 1922
ActiveWindow.ScrollRow = 1919
ActiveWindow.ScrollRow = 1915
ActiveWindow.ScrollRow = 1909
ActiveWindow.ScrollRow = 1906
ActiveWindow.ScrollRow = 1903
ActiveWindow.ScrollRow = 1896
ActiveWindow.ScrollRow = 1893
ActiveWindow.ScrollRow = 1890
ActiveWindow.ScrollRow = 1886
ActiveWindow.ScrollRow = 1880
ActiveWindow.ScrollRow = 1877
ActiveWindow.ScrollRow = 1873
ActiveWindow.ScrollRow = 1867
ActiveWindow.ScrollRow = 1860
ActiveWindow.ScrollRow = 1857
ActiveWindow.ScrollRow = 1851
ActiveWindow.ScrollRow = 1848
ActiveWindow.ScrollRow = 1838
ActiveWindow.ScrollRow = 1835
ActiveWindow.ScrollRow = 1828
ActiveWindow.ScrollRow = 1822
ActiveWindow.ScrollRow = 1815
ActiveWindow.ScrollRow = 1809
ActiveWindow.ScrollRow = 1802
ActiveWindow.ScrollRow = 1796
ActiveWindow.ScrollRow = 1793
ActiveWindow.ScrollRow = 1789
ActiveWindow.ScrollRow = 1783
ActiveWindow.ScrollRow = 1776
ActiveWindow.ScrollRow = 1770
ActiveWindow.ScrollRow = 1767
ActiveWindow.ScrollRow = 1763
ActiveWindow.ScrollRow = 1757
ActiveWindow.ScrollRow = 1754
ActiveWindow.ScrollRow = 1751
ActiveWindow.ScrollRow = 1744
ActiveWindow.ScrollRow = 1741
ActiveWindow.ScrollRow = 1738
ActiveWindow.ScrollRow = 1734
ActiveWindow.ScrollRow = 1728
ActiveWindow.ScrollRow = 1725
ActiveWindow.ScrollRow = 1721
ActiveWindow.ScrollRow = 1718
ActiveWindow.ScrollRow = 1715
ActiveWindow.ScrollRow = 1712
ActiveWindow.ScrollRow = 1708
ActiveWindow.ScrollRow = 1702
ActiveWindow.ScrollRow = 1699
ActiveWindow.ScrollRow = 1692
ActiveWindow.ScrollRow = 1689
ActiveWindow.ScrollRow = 1683
ActiveWindow.ScrollRow = 1679
ActiveWindow.ScrollRow = 1676
ActiveWindow.ScrollRow = 1673
ActiveWindow.ScrollRow = 1670
ActiveWindow.ScrollRow = 1666
ActiveWindow.ScrollRow = 1660
ActiveWindow.ScrollRow = 1654
ActiveWindow.ScrollRow = 1650
ActiveWindow.ScrollRow = 1647
ActiveWindow.ScrollRow = 1641
ActiveWindow.ScrollRow = 1637
ActiveWindow.ScrollRow = 1634
ActiveWindow.ScrollRow = 1631
ActiveWindow.ScrollRow = 1628
ActiveWindow.ScrollRow = 1624
ActiveWindow.ScrollRow = 1618
ActiveWindow.ScrollRow = 1615
ActiveWindow.ScrollRow = 1611
ActiveWindow.ScrollRow = 1608
ActiveWindow.ScrollRow = 1602
ActiveWindow.ScrollRow = 1599
ActiveWindow.ScrollRow = 1592
ActiveWindow.ScrollRow = 1586
ActiveWindow.ScrollRow = 1576
ActiveWindow.ScrollRow = 1569
ActiveWindow.ScrollRow = 1563
ActiveWindow.ScrollRow = 1556
ActiveWindow.ScrollRow = 1550
ActiveWindow.ScrollRow = 1540
ActiveWindow.ScrollRow = 1531
ActiveWindow.ScrollRow = 1524
ActiveWindow.ScrollRow = 1518
ActiveWindow.ScrollRow = 1508
ActiveWindow.ScrollRow = 1502
ActiveWindow.ScrollRow = 1492
ActiveWindow.ScrollRow = 1485
ActiveWindow.ScrollRow = 1476
ActiveWindow.ScrollRow = 1469
ActiveWindow.ScrollRow = 1463
ActiveWindow.ScrollRow = 1456
ActiveWindow.ScrollRow = 1450
ActiveWindow.ScrollRow = 1440
ActiveWindow.ScrollRow = 1434
ActiveWindow.ScrollRow = 1427
ActiveWindow.ScrollRow = 1421
ActiveWindow.ScrollRow = 1414
ActiveWindow.ScrollRow = 1408
ActiveWindow.ScrollRow = 1404
ActiveWindow.ScrollRow = 1395
ActiveWindow.ScrollRow = 1388
ActiveWindow.ScrollRow = 1385
ActiveWindow.ScrollRow = 1379
ActiveWindow.ScrollRow = 1375
ActiveWindow.ScrollRow = 1366
ActiveWindow.ScrollRow = 1362
ActiveWindow.ScrollRow = 1359
ActiveWindow.ScrollRow = 1356
ActiveWindow.ScrollRow = 1350
ActiveWindow.ScrollRow = 1346
ActiveWindow.ScrollRow = 1343
ActiveWindow.ScrollRow = 1337
ActiveWindow.ScrollRow = 1330
ActiveWindow.ScrollRow = 1327
ActiveWindow.ScrollRow = 1324
ActiveWindow.ScrollRow = 1320
ActiveWindow.ScrollRow = 1314
ActiveWindow.ScrollRow = 1311
ActiveWindow.ScrollRow = 1307
ActiveWindow.ScrollRow = 1304
ActiveWindow.ScrollRow = 1301
ActiveWindow.ScrollRow = 1298
ActiveWindow.ScrollRow = 1291
ActiveWindow.ScrollRow = 1288
ActiveWindow.ScrollRow = 1282
ActiveWindow.ScrollRow = 1275
ActiveWindow.ScrollRow = 1269
ActiveWindow.ScrollRow = 1265
ActiveWindow.ScrollRow = 1262
ActiveWindow.ScrollRow = 1256
ActiveWindow.ScrollRow = 1249
ActiveWindow.ScrollRow = 1246
ActiveWindow.ScrollRow = 1240
ActiveWindow.ScrollRow = 1236
ActiveWindow.ScrollRow = 1230
ActiveWindow.ScrollRow = 1223
ActiveWindow.ScrollRow = 1217
ActiveWindow.ScrollRow = 1214
ActiveWindow.ScrollRow = 1210
ActiveWindow.ScrollRow = 1204
ActiveWindow.ScrollRow = 1201
ActiveWindow.ScrollRow = 1194
ActiveWindow.ScrollRow = 1188
ActiveWindow.ScrollRow = 1181
ActiveWindow.ScrollRow = 1172
ActiveWindow.ScrollRow = 1165
ActiveWindow.ScrollRow = 1159
ActiveWindow.ScrollRow = 1149
ActiveWindow.ScrollRow = 1143
ActiveWindow.ScrollRow = 1136
ActiveWindow.ScrollRow = 1126
ActiveWindow.ScrollRow = 1120
ActiveWindow.ScrollRow = 1113
ActiveWindow.ScrollRow = 1107
ActiveWindow.ScrollRow = 1101
ActiveWindow.ScrollRow = 1091
ActiveWindow.ScrollRow = 1084
ActiveWindow.ScrollRow = 1078
ActiveWindow.ScrollRow = 1075
ActiveWindow.ScrollRow = 1068
ActiveWindow.ScrollRow = 1058
ActiveWindow.ScrollRow = 1055
ActiveWindow.ScrollRow = 1049
ActiveWindow.ScrollRow = 1046
ActiveWindow.ScrollRow = 1036
ActiveWindow.ScrollRow = 1033
ActiveWindow.ScrollRow = 1026
ActiveWindow.ScrollRow = 1023
ActiveWindow.ScrollRow = 1013
ActiveWindow.ScrollRow = 1010
ActiveWindow.ScrollRow = 1007
ActiveWindow.ScrollRow = 1000
ActiveWindow.ScrollRow = 997
ActiveWindow.ScrollRow = 994
ActiveWindow.ScrollRow = 991
ActiveWindow.ScrollRow = 994
ActiveWindow.ScrollRow = 1003
ActiveWindow.ScrollRow = 1010
ActiveWindow.ScrollRow = 1016
ActiveWindow.ScrollRow = 1023
ActiveWindow.ScrollRow = 1033
ActiveWindow.ScrollRow = 1042
ActiveWindow.ScrollRow = 1049
ActiveWindow.ScrollRow = 1055
ActiveWindow.ScrollRow = 1058
ActiveWindow.ScrollRow = 1062
ActiveWindow.ScrollRow = 1065
ActiveWindow.ScrollRow = 1068
ActiveWindow.ScrollRow = 1075
ActiveWindow.ScrollRow = 1081
ActiveWindow.ScrollRow = 1084
ActiveWindow.ScrollRow = 1088
ActiveWindow.ScrollRow = 1094
ActiveWindow.ScrollRow = 1097
ActiveWindow.ScrollRow = 1101
ActiveWindow.ScrollRow = 1104
ActiveWindow.ScrollRow = 1107
ActiveWindow.ScrollRow = 1110
ActiveWindow.ScrollRow = 1113
ActiveWindow.ScrollRow = 1117
ActiveWindow.ScrollRow = 1120
ActiveWindow.ScrollRow = 1123
ActiveWindow.ScrollRow = 1126
ActiveWindow.ScrollRow = 1130
ActiveWindow.ScrollRow = 1133
ActiveWindow.ScrollRow = 1139
ActiveWindow.ScrollRow = 1136
ActiveWindow.ScrollRow = 1133
ActiveWindow.ScrollRow = 1130
ActiveWindow.ScrollRow = 1126
ActiveWindow.ScrollRow = 1120
ActiveWindow.ScrollRow = 1117
ActiveWindow.ScrollRow = 1110
ActiveWindow.ScrollRow = 1107
ActiveWindow.ScrollRow = 1101
ActiveWindow.ScrollRow = 1097
ActiveWindow.ScrollRow = 1088
ActiveWindow.ScrollRow = 1084
ActiveWindow.ScrollRow = 1078
ActiveWindow.ScrollRow = 1071
ActiveWindow.ScrollRow = 1065
ActiveWindow.ScrollRow = 1058
ActiveWindow.ScrollRow = 1052
ActiveWindow.ScrollRow = 1049
ActiveWindow.ScrollRow = 1039
ActiveWindow.ScrollRow = 1036
ActiveWindow.ScrollRow = 1033
ActiveWindow.ScrollRow = 1023
ActiveWindow.ScrollRow = 1016
ActiveWindow.ScrollRow = 1007
ActiveWindow.ScrollRow = 1000
ActiveWindow.ScrollRow = 987
ActiveWindow.ScrollRow = 974
ActiveWindow.ScrollRow = 958
ActiveWindow.ScrollRow = 942
ActiveWindow.ScrollRow = 923
ActiveWindow.ScrollRow = 906
ActiveWindow.ScrollRow = 890
ActiveWindow.ScrollRow = 868
ActiveWindow.ScrollRow = 852
ActiveWindow.ScrollRow = 829
ActiveWindow.ScrollRow = 816
ActiveWindow.ScrollRow = 793
ActiveWindow.ScrollRow = 774
ActiveWindow.ScrollRow = 758
ActiveWindow.ScrollRow = 738
ActiveWindow.ScrollRow = 716
ActiveWindow.ScrollRow = 700
ActiveWindow.ScrollRow = 677
ActiveWindow.ScrollRow = 654
ActiveWindow.ScrollRow = 632
ActiveWindow.ScrollRow = 615
ActiveWindow.ScrollRow = 586
ActiveWindow.ScrollRow = 564
ActiveWindow.ScrollRow = 538
ActiveWindow.ScrollRow = 515
ActiveWindow.ScrollRow = 493
ActiveWindow.ScrollRow = 473
ActiveWindow.ScrollRow = 444
ActiveWindow.ScrollRow = 421
ActiveWindow.ScrollRow = 402
ActiveWindow.ScrollRow = 373
ActiveWindow.ScrollRow = 357
ActiveWindow.ScrollRow = 337
ActiveWindow.ScrollRow = 321
ActiveWindow.ScrollRow = 299
ActiveWindow.ScrollRow = 286
ActiveWindow.ScrollRow = 269
ActiveWindow.ScrollRow = 256
ActiveWindow.ScrollRow = 244
ActiveWindow.ScrollRow = 234
ActiveWindow.ScrollRow = 224
ActiveWindow.ScrollRow = 214
ActiveWindow.ScrollRow = 205
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 185
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 172
ActiveWindow.ScrollRow = 169
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 147
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 1
Columns("AD:AD").Select
Selection.Insert Shift:=xlToRight
Range("AD2").Select
ActiveCell.FormulaR1C1 = "Valeur"
Columns("AE:AF").Select
Selection.Insert Shift:=xlToRight
Range("AE2").Select
ActiveCell.FormulaR1C1 = "TARIF OS"
Range("AF2").Select
ActiveCell.FormulaR1C1 = "VALEUR"
Columns("AG:AG").Select
Selection.Insert Shift:=xlToRight
Range("AG2").Select
ActiveCell.FormulaR1C1 = "BAISSE"
Columns("AJ:AJ").Select
Selection.Style = "Comma"
Selection.Cut
Columns("AH:AH").Select
Columns("AJ:AJ").Select
Application.CutCopyMode = False
Selection.Cut
Columns("AH:AH").Select
Selection.Insert Shift:=xlToRight
Range("AK2").Select
ActiveCell.FormulaR1C1 = "ESTIMATE OBJ/REF"
Cells.Select
Range("X1").Activate
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("Y4").Select
Selection.CurrentRegion.Select
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
Columns("J:J").Select
Selection.Copy
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
Columns("AL:AL").Select
ActiveSheet.Paste
Range("AG8").Select
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:F").Select
Application.CutCopyMode = False
Selection.Columns.Group
ActiveWindow.SmallScroll ToRight:=6
Range("N2").Select
ActiveCell.FormulaR1C1 = "Stade"
Range("M2").Select
ActiveCell.FormulaR1C1 = "Nb CD"
Range("L2").Select
ActiveCell.FormulaR1C1 = "SUP"
Range("P2:R2").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("2:2").Select
Range("G2").Activate
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("P:R").Select
Selection.ColumnWidth = 6.43
Rows("2:2").RowHeight = 43.5
Rows("2:2").RowHeight = 54
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
Columns("Q:Q").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Columns("S:S").Select
Selection.Columns.Group
ActiveWindow.SmallScroll ToRight:=14
Columns("V:Y").Select
Selection.Columns.Group
ActiveWindow.SmallScroll ToRight:=2
Columns("AA:AA").Select
Selection.Columns.Group
Columns("AA:AB").Select
Selection.Columns.Group
Selection.Columns.Ungroup
Selection.Columns.Ungroup
Selection.Columns.Group
Columns("AD:AD").Select
Selection.Columns.Group
Columns("AF:AF").Select
Selection.Columns.Group
Columns("AH:AJ").Select
Selection.Columns.Group
ActiveWindow.SmallScroll ToRight:=6
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
Range("AD3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],tarif!R[-2]C[-29]:R[314]C[-28],2,FALSE)"
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC29,tarif!R1C1:R317C2,2,FALSE)"
Range("AD3").Select
Selection.AutoFill Destination:=Range("AD3:AD1142")
Range("AD3:AD1142").Select
Range("AG3").Select
ActiveCell.FormulaR1C1 = "=(RC[-3]-RC[-1])/RC[-3]"
Range("AG3").Select
Selection.Style = "Percent"
Selection.AutoFill Destination:=Range("AG3:AG1142")
Range("AG3:AG1142").Select
ActiveWindow.SmallScroll ToRight:=5
Sheets("enteteventes").Select
Selection.Copy
Sheets("collerleref").Select
Range("AM1").Select
ActiveSheet.Paste
Sheets("stocks").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("collerleref").Select
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 69
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 69
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 66
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 63
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
Range("BE2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("entetelots").Select
Selection.Copy
Sheets("collerleref").Select
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 54
Range("BI1").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
Columns("A:A").Select
Application.CutCopyMode = False
Selection.FormatConditions.Add Type:=xlTextString, String:="italie", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="ims", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll ToRight:=4
Columns("M:M").Select
Selection.NumberFormat = "0"
Selection.TextToColumns Destination:=Range("M1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16751204
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10284031
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
Columns("N:N").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="BLOCAGE", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="SUPP", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 6684927
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="ANNULE", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColor = 26367
.ColorIndex = xlAutomatic
.PatternTintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="REMPLAC", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16751204
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10284031
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="SAE", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.Color = -16750849
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.Color = -16750849
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Color = -16750849
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Color = -16750849
.TintAndShade = 0
.Weight = xlThin
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("O:O").Select
Selection.Insert Shift:=xlToRight
Range("O3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""SAE"",""STOCK?"","""")"
Range("O3").Select
ActiveCell.FormulaR1C1 = "=IF(RC14=""SAE"",""STOCK?"","""")"
Range("O3").Select
Selection.AutoFill Destination:=Range("O3:O1142")
Range("O3:O1142").Select
Range("O2").Select
ActiveCell.FormulaR1C1 = "STADE2"
Columns("O:O").Select
Selection.Columns.Group
ActiveWindow.SmallScroll ToRight:=16
Range("AD2").Select
ActiveCell.FormulaR1C1 = "Tarif de référence AVANT"
Range("AD3").Select
Columns("AD:AE").Select
Selection.Copy
Columns("AL:AL").Select
Selection.Insert Shift:=xlToRight
Range("AL2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Tarif de référence APRES"
Columns("AL:AL").Select
Selection.Insert Shift:=xlToRight
Selection.Columns.Ungroup
Range("AL2").Select
ActiveCell.FormulaR1C1 = "PB DIF PRIX REF ET FACT"
Range("AL3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-8]=RC[1],"""",""VOIR SI AUTRE OP"")"
Range("AL3").Select
ActiveCell.FormulaR1C1 = "=IF(RC30=RC39,"""",""VOIR SI AUTRE OP"")"
Range("AL3").Select
With Selection.Font
.Color = -10092289
.TintAndShade = 0
End With
Selection.AutoFill Destination:=Range("AL3:AL1142")
Range("AL3:AL1142").Select
Range("AP6").Select
Selection.CurrentRegion.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
Rows("1:1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveWindow.SmallScroll ToRight:=4
Range("G3").Select
ActiveCell.FormulaR1C1 = "1"
Range("G4").Select
ActiveCell.FormulaR1C1 = "2"
Range("G3:G4").Select
Selection.AutoFill Destination:=Range("G3:G1142")
Range("G3:G1142").Select
ActiveWindow.SmallScroll ToRight:=10
Range("U3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Font.Bold = True
Range("V3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Font.Italic = True
Columns("AI:AI").Select
With Selection.Font
.Color = -11489280
.TintAndShade = 0
End With
Columns("AD:AH").Select
Selection.ColumnWidth = 8
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveWindow.SmallScroll ToRight:=7
Columns("AJ:AN").Select
Selection.ColumnWidth = 8
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("AO:AO").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("AQ:BQ").Select
Selection.Columns.Group
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 62
Columns("BQ:BQ").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("BQ2").Select
ActiveCell.FormulaR1C1 = "1"
Range("BR2").Select
ActiveCell.FormulaR1C1 = "2"
Range("BQ2:BR2").Select
Selection.AutoFill Destination:=Range("BQ2:CH2"), Type:=xlFillDefault
Range("BQ2:CH2").Select
Columns("BQ:CH").Select
Range("CH1").Activate
Columns("BQ:CH").EntireColumn.AutoFit
Range("CA2").Select
ActiveWindow.ScrollColumn = 63
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll ToRight:=20
Columns("AI:AI").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll ToRight:=-11
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 160
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 182
ActiveWindow.ScrollRow = 185
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 192
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 198
ActiveWindow.ScrollRow = 202
ActiveWindow.ScrollRow = 205
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 215
ActiveWindow.ScrollRow = 218
ActiveWindow.ScrollRow = 221
ActiveWindow.ScrollRow = 224
ActiveWindow.ScrollRow = 231
ActiveWindow.ScrollRow = 237
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 247
ActiveWindow.ScrollRow = 253
ActiveWindow.ScrollRow = 260
ActiveWindow.ScrollRow = 266
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 279
ActiveWindow.ScrollRow = 283
ActiveWindow.ScrollRow = 286
ActiveWindow.ScrollRow = 292
ActiveWindow.ScrollRow = 299
ActiveWindow.ScrollRow = 305
ActiveWindow.ScrollRow = 315
ActiveWindow.ScrollRow = 321
ActiveWindow.ScrollRow = 328
ActiveWindow.ScrollRow = 341
ActiveWindow.ScrollRow = 347
ActiveWindow.ScrollRow = 354
ActiveWindow.ScrollRow = 360
ActiveWindow.ScrollRow = 364
ActiveWindow.ScrollRow = 376
ActiveWindow.ScrollRow = 383
ActiveWindow.ScrollRow = 389
ActiveWindow.ScrollRow = 399
ActiveWindow.ScrollRow = 406
ActiveWindow.ScrollRow = 412
ActiveWindow.ScrollRow = 419
ActiveWindow.ScrollRow = 425
ActiveWindow.ScrollRow = 435
ActiveWindow.ScrollRow = 441
ActiveWindow.ScrollRow = 448
ActiveWindow.ScrollRow = 457
ActiveWindow.ScrollRow = 464
ActiveWindow.ScrollRow = 470
ActiveWindow.ScrollRow = 477
ActiveWindow.ScrollRow = 483
ActiveWindow.ScrollRow = 493
ActiveWindow.ScrollRow = 499
ActiveWindow.ScrollRow = 506
ActiveWindow.ScrollRow = 509
ActiveWindow.ScrollRow = 516
ActiveWindow.ScrollRow = 522
ActiveWindow.ScrollRow = 529
ActiveWindow.ScrollRow = 535
ActiveWindow.ScrollRow = 542
ActiveWindow.ScrollRow = 551
ActiveWindow.ScrollRow = 564
ActiveWindow.ScrollRow = 580
ActiveWindow.ScrollRow = 593
ActiveWindow.ScrollRow = 606
ActiveWindow.ScrollRow = 613
ActiveWindow.ScrollRow = 622
ActiveWindow.ScrollRow = 626
ActiveWindow.ScrollRow = 629
ActiveWindow.ScrollRow = 635
ActiveWindow.ScrollRow = 642
ActiveWindow.ScrollRow = 652
ActiveWindow.ScrollRow = 655
ActiveWindow.ScrollRow = 665
ActiveWindow.ScrollRow = 674
ActiveWindow.ScrollRow = 681
ActiveWindow.ScrollRow = 694
ActiveWindow.ScrollRow = 707
ActiveWindow.ScrollRow = 716
ActiveWindow.ScrollRow = 729
ActiveWindow.ScrollRow = 749
ActiveWindow.ScrollRow = 758
ActiveWindow.ScrollRow = 775
ActiveWindow.ScrollRow = 788
ActiveWindow.ScrollRow = 797
ActiveWindow.ScrollRow = 810
ActiveWindow.ScrollRow = 826
ActiveWindow.ScrollRow = 836
ActiveWindow.ScrollRow = 846
ActiveWindow.ScrollRow = 852
ActiveWindow.ScrollRow = 859
ActiveWindow.ScrollRow = 865
ActiveWindow.ScrollRow = 872
ActiveWindow.ScrollRow = 875
ActiveWindow.ScrollRow = 885
ActiveWindow.ScrollRow = 888
ActiveWindow.ScrollRow = 891
ActiveWindow.ScrollRow = 898
ActiveWindow.ScrollRow = 904
ActiveWindow.ScrollRow = 907
ActiveWindow.ScrollRow = 911
ActiveWindow.ScrollRow = 917
ActiveWindow.ScrollRow = 923
ActiveWindow.ScrollRow = 930
ActiveWindow.ScrollRow = 936
ActiveWindow.ScrollRow = 943
ActiveWindow.ScrollRow = 956
ActiveWindow.ScrollRow = 966
ActiveWindow.ScrollRow = 975
ActiveWindow.ScrollRow = 988
ActiveWindow.ScrollRow = 1001
ActiveWindow.ScrollRow = 1011
ActiveWindow.ScrollRow = 1024
ActiveWindow.ScrollRow = 1034
ActiveWindow.ScrollRow = 1046
ActiveWindow.ScrollRow = 1063
ActiveWindow.ScrollRow = 1072
ActiveWindow.ScrollRow = 1085
ActiveWindow.ScrollRow = 1095
ActiveWindow.ScrollRow = 1105
ActiveWindow.ScrollRow = 1114
ActiveWindow.ScrollRow = 1127
ActiveWindow.ScrollRow = 1134
ActiveWindow.ScrollRow = 1144
ActiveWindow.ScrollRow = 1150
ActiveWindow.ScrollRow = 1153
ActiveWindow.ScrollRow = 1160
ActiveWindow.ScrollRow = 1163
ActiveWindow.ScrollRow = 1166
ActiveWindow.ScrollRow = 1169
ActiveWindow.ScrollRow = 1173
ActiveWindow.ScrollRow = 1176
ActiveWindow.ScrollRow = 1179
ActiveWindow.ScrollRow = 1182
ActiveWindow.ScrollRow = 1189
ActiveWindow.ScrollRow = 1192
ActiveWindow.ScrollRow = 1195
ActiveWindow.ScrollRow = 1199
ActiveWindow.ScrollRow = 1202
ActiveWindow.ScrollRow = 1205
ActiveWindow.ScrollRow = 1208
ActiveWindow.ScrollRow = 1212
ActiveWindow.ScrollRow = 1215
ActiveWindow.ScrollRow = 1212
ActiveWindow.ScrollRow = 1205
ActiveWindow.ScrollRow = 1195
ActiveWindow.ScrollRow = 1182
ActiveWindow.ScrollRow = 1173
ActiveWindow.ScrollRow = 1157
ActiveWindow.ScrollRow = 1144
ActiveWindow.ScrollRow = 1131
ActiveWindow.ScrollRow = 1114
ActiveWindow.ScrollRow = 1101
ActiveWindow.ScrollRow = 1089
ActiveWindow.ScrollRow = 1076
ActiveWindow.ScrollRow = 1066
ActiveWindow.ScrollRow = 1053
ActiveWindow.ScrollRow = 1050
ActiveWindow.ScrollRow = 1043
ActiveWindow.ScrollRow = 1040
ActiveWindow.ScrollRow = 1037
ActiveWindow.ScrollRow = 1034
ActiveWindow.ScrollRow = 1030
ActiveWindow.ScrollRow = 1024
ActiveWindow.ScrollRow = 1017
ActiveWindow.ScrollRow = 1014
ActiveWindow.ScrollRow = 1011
ActiveWindow.ScrollRow = 1017
ActiveWindow.ScrollRow = 1024
ActiveWindow.ScrollRow = 1030
ActiveWindow.ScrollRow = 1037
ActiveWindow.ScrollRow = 1040
ActiveWindow.ScrollRow = 1050
ActiveWindow.ScrollRow = 1053
ActiveWindow.ScrollRow = 1059
ActiveWindow.ScrollRow = 1063
ActiveWindow.ScrollRow = 1069
ActiveWindow.ScrollRow = 1079
ActiveWindow.ScrollRow = 1085
ActiveWindow.ScrollRow = 1089
ActiveWindow.ScrollRow = 1092
ActiveWindow.ScrollRow = 1098
ActiveWindow.ScrollRow = 1101
ActiveWindow.ScrollRow = 1108
ActiveWindow.ScrollRow = 1114
ActiveWindow.ScrollRow = 1118
ActiveWindow.ScrollRow = 1121
ActiveWindow.ScrollRow = 1124
ActiveWindow.ScrollRow = 1127
ActiveWindow.ScrollRow = 1131
ActiveWindow.ScrollRow = 1134
ActiveWindow.ScrollRow = 1137
ActiveWindow.ScrollRow = 1140
ActiveWindow.ScrollRow = 1144
ActiveWindow.ScrollRow = 1147
ActiveWindow.ScrollRow = 1150
ActiveWindow.ScrollRow = 1153
ActiveWindow.ScrollRow = 1157
ActiveWindow.ScrollRow = 1160
ActiveWindow.ScrollRow = 1163
ActiveWindow.ScrollRow = 1166
ActiveWindow.ScrollRow = 1169
ActiveWindow.ScrollRow = 1166
ActiveWindow.ScrollRow = 1163
ActiveWindow.ScrollRow = 1160
ActiveWindow.ScrollRow = 1157
ActiveWindow.ScrollRow = 1153
ActiveWindow.ScrollRow = 1150
ActiveWindow.ScrollRow = 1147
ActiveWindow.ScrollRow = 1144
ActiveWindow.ScrollRow = 1140
ActiveWindow.ScrollRow = 1137
ActiveWindow.ScrollRow = 1134
ActiveWindow.ScrollRow = 1131
ActiveWindow.ScrollRow = 1124
ActiveWindow.ScrollRow = 1118
ActiveWindow.ScrollRow = 1108
ActiveWindow.ScrollRow = 1098
ActiveWindow.ScrollRow = 1085
ActiveWindow.ScrollRow = 1072
ActiveWindow.ScrollRow = 1053
ActiveWindow.ScrollRow = 1034
ActiveWindow.ScrollRow = 1011
ActiveWindow.ScrollRow = 988
ActiveWindow.ScrollRow = 972
ActiveWindow.ScrollRow = 943
ActiveWindow.ScrollRow = 920
ActiveWindow.ScrollRow = 898
ActiveWindow.ScrollRow = 881
ActiveWindow.ScrollRow = 855
ActiveWindow.ScrollRow = 833
ActiveWindow.ScrollRow = 810
ActiveWindow.ScrollRow = 784
ActiveWindow.ScrollRow = 762
ActiveWindow.ScrollRow = 733
ActiveWindow.ScrollRow = 707
ActiveWindow.ScrollRow = 684
ActiveWindow.ScrollRow = 655
ActiveWindow.ScrollRow = 632
ActiveWindow.ScrollRow = 606
ActiveWindow.ScrollRow = 587
ActiveWindow.ScrollRow = 561
ActiveWindow.ScrollRow = 538
ActiveWindow.ScrollRow = 516
ActiveWindow.ScrollRow = 493
ActiveWindow.ScrollRow = 474
ActiveWindow.ScrollRow = 451
ActiveWindow.ScrollRow = 428
ActiveWindow.ScrollRow = 409
ActiveWindow.ScrollRow = 389
ActiveWindow.ScrollRow = 367
ActiveWindow.ScrollRow = 347
ActiveWindow.ScrollRow = 331
ActiveWindow.ScrollRow = 312
ActiveWindow.ScrollRow = 299
ActiveWindow.ScrollRow = 286
ActiveWindow.ScrollRow = 266
ActiveWindow.ScrollRow = 260
ActiveWindow.ScrollRow = 247
ActiveWindow.ScrollRow = 237
ActiveWindow.ScrollRow = 231
ActiveWindow.ScrollRow = 228
ActiveWindow.ScrollRow = 221
ActiveWindow.ScrollRow = 215
ActiveWindow.ScrollRow = 211
ActiveWindow.ScrollRow = 205
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 1
Cells.Select
Range("J1").Activate
Selection.ColumnWidth = 11.29
Selection.ColumnWidth = 11
Rows("2:2").Select
Range("J2").Activate
Selection.AutoFilter
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
Columns("Q:S").Select
Selection.ColumnWidth = 5
Selection.ColumnWidth = 6
Columns("G:I").Select
Columns("G:I").EntireColumn.AutoFit
Columns("K:K").Select
Selection.Columns.Group
Columns("L:L").Select
Columns("L:L").EntireColumn.AutoFit
Columns("M:M").Select
Columns("M:M").EntireColumn.AutoFit
Columns("N:N").Select
Columns("N:N").EntireColumn.AutoFit
Columns("M:p").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("J4").Select
Columns("P:p").EntireColumn.AutoFit
Columns("P:p").ColumnWidth = 8.86
Columns("M:M").ColumnWidth = 5
Range("L4").Select
Columns("M:M").ColumnWidth = 4.14
Range("R7").Select
ActiveWindow.SmallScroll ToRight:=1
Columns("V:V").ColumnWidth = 16.14
ActiveWindow.SmallScroll ToRight:=5
Columns("AD:AD").Select
Selection.ColumnWidth = 8
Columns("AF:AF").Select
Selection.ColumnWidth = 8
Columns("AH:AH").Select
Selection.ColumnWidth = 8
Columns("AF:AF").Select
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
Columns("AH:AH").Select
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
Columns("AO:AO").Select
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
Columns("AP:AP").Select
Selection.Columns.Group
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
Columns("AM:AM").Select
Selection.ColumnWidth = 8
Columns("AO:AO").Select
Selection.Insert Shift:=xlToRight
Selection.Columns.Ungroup
Range("AO2").Select
ActiveCell.FormulaR1C1 = "AC"
Columns("AO:AO").Select
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
Columns("AO:AO").EntireColumn.AutoFit
Selection.ColumnWidth = 5.86
Range("AO3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-11]=RC[-2],"""",""AC"")"
Range("AO3").Select
Selection.AutoFill Destination:=Range("AO3:AO1142")
Range("AO3:AO1142").Select
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 147
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 163
ActiveWindow.ScrollRow = 169
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 182
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 202
ActiveWindow.ScrollRow = 205
ActiveWindow.ScrollRow = 211
ActiveWindow.ScrollRow = 221
ActiveWindow.ScrollRow = 228
ActiveWindow.ScrollRow = 234
ActiveWindow.ScrollRow = 237
ActiveWindow.ScrollRow = 244
ActiveWindow.ScrollRow = 257
ActiveWindow.ScrollRow = 266
ActiveWindow.ScrollRow = 279
ActiveWindow.ScrollRow = 289
ActiveWindow.ScrollRow = 305
ActiveWindow.ScrollRow = 328
ActiveWindow.ScrollRow = 351
ActiveWindow.ScrollRow = 373
ActiveWindow.ScrollRow = 399
ActiveWindow.ScrollRow = 422
ActiveWindow.ScrollRow = 444
ActiveWindow.ScrollRow = 470
ActiveWindow.ScrollRow = 487
ActiveWindow.ScrollRow = 506
ActiveWindow.ScrollRow = 522
ActiveWindow.ScrollRow = 535
ActiveWindow.ScrollRow = 542
ActiveWindow.ScrollRow = 558
ActiveWindow.ScrollRow = 567
ActiveWindow.ScrollRow = 574
ActiveWindow.ScrollRow = 584
ActiveWindow.ScrollRow = 593
ActiveWindow.ScrollRow = 600
ActiveWindow.ScrollRow = 606
ActiveWindow.ScrollRow = 610
ActiveWindow.ScrollRow = 616
ActiveWindow.ScrollRow = 622
ActiveWindow.ScrollRow = 626
ActiveWindow.ScrollRow = 629
ActiveWindow.ScrollRow = 632
ActiveWindow.ScrollRow = 645
ActiveWindow.ScrollRow = 652
ActiveWindow.ScrollRow = 661
ActiveWindow.ScrollRow = 668
ActiveWindow.ScrollRow = 677
ActiveWindow.ScrollRow = 690
ActiveWindow.ScrollRow = 703
ActiveWindow.ScrollRow = 723
ActiveWindow.ScrollRow = 736
ActiveWindow.ScrollRow = 758
ActiveWindow.ScrollRow = 778
ActiveWindow.ScrollRow = 797
ActiveWindow.ScrollRow = 820
ActiveWindow.ScrollRow = 839
ActiveWindow.ScrollRow = 865
ActiveWindow.ScrollRow = 888
ActiveWindow.ScrollRow = 911
ActiveWindow.ScrollRow = 933
ActiveWindow.ScrollRow = 949
ActiveWindow.ScrollRow = 972
ActiveWindow.ScrollRow = 991
ActiveWindow.ScrollRow = 1008
ActiveWindow.ScrollRow = 1024
ActiveWindow.ScrollRow = 1037
ActiveWindow.ScrollRow = 1053
ActiveWindow.ScrollRow = 1069
ActiveWindow.ScrollRow = 1082
ActiveWindow.ScrollRow = 1101
ActiveWindow.ScrollRow = 1118
ActiveWindow.ScrollRow = 1140
ActiveWindow.ScrollRow = 1160
ActiveWindow.ScrollRow = 1182
ActiveWindow.ScrollRow = 1208
ActiveWindow.ScrollRow = 1231
ActiveWindow.ScrollRow = 1257
ActiveWindow.ScrollRow = 1283
ActiveWindow.ScrollRow = 1309
ActiveWindow.ScrollRow = 1341
ActiveWindow.ScrollRow = 1373
ActiveWindow.ScrollRow = 1403
ActiveWindow.ScrollRow = 1432
ActiveWindow.ScrollRow = 1461
ActiveWindow.ScrollRow = 1490
ActiveWindow.ScrollRow = 1516
ActiveWindow.ScrollRow = 1538
ActiveWindow.ScrollRow = 1561
ActiveWindow.ScrollRow = 1584
ActiveWindow.ScrollRow = 1600
ActiveWindow.ScrollRow = 1610
ActiveWindow.ScrollRow = 1623
ActiveWindow.ScrollRow = 1636
ActiveWindow.ScrollRow = 1645
ActiveWindow.ScrollRow = 1658
ActiveWindow.ScrollRow = 1665
ActiveWindow.ScrollRow = 1674
ActiveWindow.ScrollRow = 1678
ActiveWindow.ScrollRow = 1681
ActiveWindow.ScrollRow = 1678
ActiveWindow.ScrollRow = 1661
ActiveWindow.ScrollRow = 1642
ActiveWindow.ScrollRow = 1626
ActiveWindow.ScrollRow = 1593
ActiveWindow.ScrollRow = 1571
ActiveWindow.ScrollRow = 1545
ActiveWindow.ScrollRow = 1519
ActiveWindow.ScrollRow = 1500
ActiveWindow.ScrollRow = 1477
ActiveWindow.ScrollRow = 1458
ActiveWindow.ScrollRow = 1451
ActiveWindow.ScrollRow = 1441
ActiveWindow.ScrollRow = 1438
ActiveWindow.ScrollRow = 1435
ActiveWindow.ScrollRow = 1432
ActiveWindow.ScrollRow = 1428
ActiveWindow.ScrollRow = 1422
ActiveWindow.ScrollRow = 1419
ActiveWindow.ScrollRow = 1412
ActiveWindow.ScrollRow = 1409
ActiveWindow.ScrollRow = 1403
ActiveWindow.ScrollRow = 1399
ActiveWindow.ScrollRow = 1396
ActiveWindow.ScrollRow = 1393
ActiveWindow.ScrollRow = 1390
ActiveWindow.ScrollRow = 1386
ActiveWindow.ScrollRow = 1383
ActiveWindow.ScrollRow = 1380
ActiveWindow.ScrollRow = 1377
ActiveWindow.ScrollRow = 1373
ActiveWindow.ScrollRow = 1370
ActiveWindow.ScrollRow = 1364
ActiveWindow.ScrollRow = 1354
ActiveWindow.ScrollRow = 1351
ActiveWindow.ScrollRow = 1344
ActiveWindow.ScrollRow = 1341
ActiveWindow.ScrollRow = 1335
ActiveWindow.ScrollRow = 1328
ActiveWindow.ScrollRow = 1325
ActiveWindow.ScrollRow = 1322
ActiveWindow.ScrollRow = 1315
ActiveWindow.ScrollRow = 1309
ActiveWindow.ScrollRow = 1305
ActiveWindow.ScrollRow = 1302
ActiveWindow.ScrollRow = 1299
ActiveWindow.ScrollRow = 1296
ActiveWindow.ScrollRow = 1292
ActiveWindow.ScrollRow = 1289
ActiveWindow.ScrollRow = 1286
ActiveWindow.ScrollRow = 1283
ActiveWindow.ScrollRow = 1280
ActiveWindow.ScrollRow = 1276
ActiveWindow.ScrollRow = 1273
ActiveWindow.ScrollRow = 1270
ActiveWindow.ScrollRow = 1267
ActiveWindow.ScrollRow = 1263
ActiveWindow.ScrollRow = 1260
ActiveWindow.ScrollRow = 1257
ActiveWindow.ScrollRow = 1254
ActiveWindow.ScrollRow = 1250
ActiveWindow.ScrollRow = 1247
ActiveWindow.ScrollRow = 1244
ActiveWindow.ScrollRow = 1241
ActiveWindow.ScrollRow = 1237
ActiveWindow.ScrollRow = 1231
ActiveWindow.ScrollRow = 1228
ActiveWindow.ScrollRow = 1221
ActiveWindow.ScrollRow = 1212
ActiveWindow.ScrollRow = 1205
ActiveWindow.ScrollRow = 1199
ActiveWindow.ScrollRow = 1186
ActiveWindow.ScrollRow = 1173
ActiveWindow.ScrollRow = 1163
ActiveWindow.ScrollRow = 1153
ActiveWindow.ScrollRow = 1140
ActiveWindow.ScrollRow = 1134
ActiveWindow.ScrollRow = 1124
ActiveWindow.ScrollRow = 1118
ActiveWindow.ScrollRow = 1114
ActiveWindow.ScrollRow = 1108
ActiveWindow.ScrollRow = 1101
ActiveWindow.ScrollRow = 1098
ActiveWindow.ScrollRow = 1095
ActiveWindow.ScrollRow = 1092
ActiveWindow.ScrollRow = 1089
ActiveWindow.ScrollRow = 1092
ActiveWindow.ScrollRow = 1098
ActiveWindow.ScrollRow = 1101
ActiveWindow.ScrollRow = 1111
ActiveWindow.ScrollRow = 1114
ActiveWindow.ScrollRow = 1118
ActiveWindow.ScrollRow = 1121
ActiveWindow.ScrollRow = 1124
ActiveWindow.ScrollRow = 1127
ActiveWindow.ScrollRow = 1131
ActiveWindow.ScrollRow = 1134
ActiveWindow.ScrollRow = 1137
ActiveWindow.ScrollRow = 1140
ActiveWindow.ScrollRow = 1137
ActiveWindow.ScrollRow = 1134
ActiveWindow.ScrollRow = 1131
ActiveWindow.ScrollRow = 1127
Range("AP1143").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1140]C:R[-1]C)"
Range("AP1144").Select
ActiveWindow.ScrollRow = 1095
ActiveWindow.ScrollRow = 1085
ActiveWindow.ScrollRow = 1066
ActiveWindow.ScrollRow = 1043
ActiveWindow.ScrollRow = 1014
ActiveWindow.ScrollRow = 978
ActiveWindow.ScrollRow = 888
ActiveWindow.ScrollRow = 794
ActiveWindow.ScrollRow = 742
ActiveWindow.ScrollRow = 639
ActiveWindow.ScrollRow = 580
ActiveWindow.ScrollRow = 483
ActiveWindow.ScrollRow = 435
ActiveWindow.ScrollRow = 389
ActiveWindow.ScrollRow = 299
ActiveWindow.ScrollRow = 260
ActiveWindow.ScrollRow = 218
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 69
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 66
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 63
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollRow = 1098
ActiveWindow.ScrollRow = 1095
ActiveWindow.ScrollRow = 1092
ActiveWindow.ScrollRow = 1085
ActiveWindow.ScrollRow = 1076
ActiveWindow.ScrollRow = 1063
ActiveWindow.ScrollRow = 1040
ActiveWindow.ScrollRow = 1027
ActiveWindow.ScrollRow = 1008
ActiveWindow.ScrollRow = 985
ActiveWindow.ScrollRow = 962
ActiveWindow.ScrollRow = 946
ActiveWindow.ScrollRow = 923
ActiveWindow.ScrollRow = 901
ActiveWindow.ScrollRow = 878
ActiveWindow.ScrollRow = 849
ActiveWindow.ScrollRow = 823
ActiveWindow.ScrollRow = 794
ActiveWindow.ScrollRow = 765
ActiveWindow.ScrollRow = 726
ActiveWindow.ScrollRow = 697
ActiveWindow.ScrollRow = 658
ActiveWindow.ScrollRow = 622
ActiveWindow.ScrollRow = 587
ActiveWindow.ScrollRow = 545
ActiveWindow.ScrollRow = 509
ActiveWindow.ScrollRow = 470
ActiveWindow.ScrollRow = 435
ActiveWindow.ScrollRow = 402
ActiveWindow.ScrollRow = 373
ActiveWindow.ScrollRow = 341
ActiveWindow.ScrollRow = 318
ActiveWindow.ScrollRow = 292
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 250
ActiveWindow.ScrollRow = 234
ActiveWindow.ScrollRow = 221
ActiveWindow.ScrollRow = 211
ActiveWindow.ScrollRow = 202
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 192
ActiveWindow.ScrollRow = 182
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 163
ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 147
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 1
Range("AR3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],originehorsitalie!R[1]C[-42]:R[9997]C[-38],3,FALSE)"
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Selection.AutoFill Destination:=Range("AR3:AU3"), Type:=xlFillDefault
Range("AR3:AU3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC43,originehorsitalie!R4C2:R10000C6,3,FALSE)"
Range("AR3:AU3").Select
Range("AS3").Activate
Selection.FillRight
Range("AS3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC43,originehorsitalie!R4C2:R10000C6,4,FALSE)"
Range("AT3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC43,originehorsitalie!R4C2:R10000C6,5,FALSE)"
Range("AU3").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-3]"
Range("AU3").Select
Selection.Style = "Percent"
Range("AS3").Select
Range("AR3:AU3").Select
Selection.AutoFill Destination:=Range("AR3:AU1142")
Range("AR3:AU1142").Select
Range("AV3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],'12m'!R[2]C[-47]:R[9997]C[-42],4,FALSE)"
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,'12m'!R5C1:R10000C6,4,FALSE)"
Range("AV3").Select
Selection.AutoFill Destination:=Range("AV3:AY3"), Type:=xlFillDefault
Range("AV3:AY3").Select
Range("AW3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,'12m'!R5C1:R10000C6,5,FALSE)"
Range("AX3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,'12m'!R5C1:R10000C6,6,FALSE)"
Range("AY3").Select
ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-3]"
Range("AY3").Select
Selection.Style = "Percent"
Range("AV3:AY3").Select
Selection.AutoFill Destination:=Range("AV3:AY1142")
Range("AV3:AY1142").Select
ActiveWindow.SmallScroll ToRight:=9
Range("AZ3").Select
ActiveWindow.SmallScroll ToRight:=-2
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-9],hyp!R[2]C[-50]:R[9997]C[-45],4,FALSE)"
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,hyp!R5C2:R10000C7,4,FALSE)"
Range("AZ3").Select
Selection.AutoFill Destination:=Range("AZ3:BB3"), Type:=xlFillDefault
Range("AZ3:BB3").Select
Range("BA3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,hyp!R5C2:R10000C7,5,FALSE)"
Range("BB3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,hyp!R5C2:R10000C7,6,FALSE)"
Range("BC3").Select
ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-3]"
Range("BD3").Select
ActiveCell.FormulaR1C1 = "=RC[-4]/RC[-8]"
Range("BC3").Select
Selection.Style = "Percent"
Range("BD3").Select
Selection.Style = "Percent"
Range("AZ3:BB3").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Range("AZ3:BD3").Select
Selection.AutoFill Destination:=Range("AZ3:BD1142")
Range("AZ3:BD1142").Select
ActiveWindow.SmallScroll ToRight:=7
Range("BE3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,spes!R5C2:R10000C7,4,FALSE)"
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Selection.AutoFill Destination:=Range("BE3:BG3"), Type:=xlFillDefault
Range("BE3:BG3").Select
Range("BF3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,spes!R5C2:R10000C7,5,FALSE)"
Range("BG3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,spes!R5C2:R10000C7,6,FALSE)"
Range("BH3").Select
ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-3]"
Range("BI3").Select
ActiveCell.FormulaR1C1 = "=RC[-4]/RC[-13]"
Range("BH3:BI3").Select
Selection.Style = "Percent"
Range("BE3:BI3").Select
Selection.AutoFill Destination:=Range("BE3:BI1142")
Range("BE3:BI1142").Select
Range("BJ3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-19],stocks!R[3]C[-58]:R[1048573]C[-32],26,FALSE)"
ActiveWindow.SmallScroll ToRight:=7
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,26,FALSE)"
Range("BJ3").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Selection.AutoFill Destination:=Range("BJ3:BM3"), Type:=xlFillDefault
Range("BJ3:BM3").Select
Range("BK3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,27,FALSE)"
Range("BL3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,28,FALSE)"
Range("BJ3").Select
Sheets("stocks").Select
ActiveWindow.ScrollRow = 1048527
ActiveWindow.ScrollRow = 1047023
ActiveWindow.ScrollRow = 1044014
ActiveWindow.ScrollRow = 1039501
ActiveWindow.ScrollRow = 1031979
ActiveWindow.ScrollRow = 1021449
ActiveWindow.ScrollRow = 1009414
ActiveWindow.ScrollRow = 992866
ActiveWindow.ScrollRow = 976319
ActiveWindow.ScrollRow = 955258
ActiveWindow.ScrollRow = 923667
ActiveWindow.ScrollRow = 892075
ActiveWindow.ScrollRow = 860484
ActiveWindow.ScrollRow = 824380
ActiveWindow.ScrollRow = 783763
ActiveWindow.ScrollRow = 740137
ActiveWindow.ScrollRow = 691998
ActiveWindow.ScrollRow = 648372
ActiveWindow.ScrollRow = 603242
ActiveWindow.ScrollRow = 552094
ActiveWindow.ScrollRow = 506964
ActiveWindow.ScrollRow = 467851
ActiveWindow.ScrollRow = 427234
ActiveWindow.ScrollRow = 388121
ActiveWindow.ScrollRow = 344495
ActiveWindow.ScrollRow = 312904
ActiveWindow.ScrollRow = 290339
ActiveWindow.ScrollRow = 269278
ActiveWindow.ScrollRow = 246713
ActiveWindow.ScrollRow = 218131
ActiveWindow.ScrollRow = 191052
ActiveWindow.ScrollRow = 165479
ActiveWindow.ScrollRow = 142913
ActiveWindow.ScrollRow = 114331
ActiveWindow.ScrollRow = 79731
ActiveWindow.ScrollRow = 57166
ActiveWindow.ScrollRow = 33097
ActiveWindow.ScrollRow = 10531
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
Sheets("collerleref").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,23,FALSE)"
Range("BK3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,24,FALSE)"
Range("BL3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,25,FALSE)"
Range("BJ3:BM3").Select
Selection.AutoFill Destination:=Range("BJ3:BM1142")
Range("BJ3:BM1142").Select
Columns("BJ:BM").Select
Selection.ColumnWidth = 8
Range("BM2").Select
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 7
Range("G2").Select
End Sub
 
Trier par date Trier par votes
C

Caillou

XLDnaute Impliqué
Re : Macro trop longue

Bonjour,

Tu peux déjà commencer par virer toutes les lignes du type ActiveWindows.Scroll...qui servent aux défilement de la feuille (mais que ne sont pas indispensables pour le déroulement de la macro)
Après on y verra plus clair !!!!

Caillou
 
Vote positif 0 Vote négatif
M

matoupitou

XLDnaute Occasionnel
Re : Macro trop longue

comme je ne souhaite pas la perdre qq précision cher caillou svp

par exemple ça je peux l'effacer inclus "toRight:=15" ?
ActiveWindow.SmallScroll ToRight:=15

et ça OUT aussi tout de go
MERCI DE CONFIRMER cet enregistrement m'a pris trop de temps!

ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
 
Vote positif 0 Vote négatif
T

Tirou

XLDnaute Occasionnel
Re : Macro trop longue

Alors petite précision :

Si tu ne veux pas perdre des données (et la macro, c'en est une), tu la sauvegarde ... Chance pour toi, tu l'as postée sur un forum : si jamais tu la perds ou qu'elle ne fonctionne plus après tes modifs, tu aura le choix entre
-CTRL-Z pour annuler les dernier changements
-Venir ici, sélectionner toute ta macro, faire copier et aller le coller en lieu et place de ta macro.

Sinon, de manière générale, on appelle ça faire une copie du fichier avant modification, juste au cas où ....
 
Vote positif 0 Vote négatif
ROGER2327

ROGER2327

XLDnaute Barbatruc
Re : Macro trop longue

Bonjour à tous.


Un premier nettoyage et un balisage plus propre :
VB: 
Sub bw_vers_excel()
'
' bw_vers_excel Macro
' mise en forme suite à l'export ajout des entente lots + ventes + stocks
'
' Touche de raccourci du clavier: Ctrl+w
'
Cells.Select
Selection.ClearFormats
With Selection.Font
.Name = "Segoe UI"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Rows("4:4").Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Columns("D").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=15
Columns("AA:AB").Select
Range("AB1").Activate
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("B:B").Select
Selection.Cut
Columns("D").Select
Selection.Insert Shift:=xlToRight
Columns("E:G").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("H:I").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("N:N").Select
Selection.Cut
Columns("P:P").Select
Selection.Insert Shift:=xlToRight
Columns("O:Q").Select
Selection.Cut
Columns("D").Select
Selection.Insert Shift:=xlToRight
Range("G2").Select
ActiveCell.FormulaR1C1 = "REF13 - UPC"
Range("I2").Select
ActiveCell.FormulaR1C1 = "1ère com"
Range("L2").Select
ActiveCell.FormulaR1C1 = "Désignation"
Columns("S:T").Select
Selection.Cut
Columns("R:R").Select
Selection.Insert Shift:=xlToRight
Columns("R:T").Select
Selection.Cut
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=12
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("G2").Select
ActiveCell.FormulaR1C1 = "NUM1"
Range("H2").Select
ActiveCell.FormulaR1C1 = "NUM2"
Range("I2").Select
ActiveCell.FormulaR1C1 = "GENRE"
Columns("P:P").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("P2").Select
ActiveCell.FormulaR1C1 = "RAFRAICH"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "VTE ORIGINE (mep)"
Range("R2").Select
ActiveCell.FormulaR1C1 = "LOTS?"
Range("R3").Select
ActiveWindow.SmallScroll ToRight:=15
Columns("AD:AD").Select
Selection.Insert Shift:=xlToRight
Range("AD2").Select
ActiveCell.FormulaR1C1 = "Valeur"
Columns("AE:AF").Select
Selection.Insert Shift:=xlToRight
Range("AE2").Select
ActiveCell.FormulaR1C1 = "TARIF OS"
Range("AF2").Select
ActiveCell.FormulaR1C1 = "VALEUR"
Columns("AG:AG").Select
Selection.Insert Shift:=xlToRight
Range("AG2").Select
ActiveCell.FormulaR1C1 = "BAISSE"
Columns("AJ:AJ").Select
Selection.Style = "Comma"
Selection.Cut
Columns("AH:AH").Select
Columns("AJ:AJ").Select
Application.CutCopyMode = False
Selection.Cut
Columns("AH:AH").Select
Selection.Insert Shift:=xlToRight
Range("AK2").Select
ActiveCell.FormulaR1C1 = "ESTIMATE OBJ/REF"
Cells.Select
Range("X1").Activate
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("Y4").Select
Selection.CurrentRegion.Select
Columns("J:J").Select
Selection.Copy
Columns("AL:AL").Select
ActiveSheet.Paste
Range("AG8").Select
Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:F").Select
Application.CutCopyMode = False
Selection.Columns.Group
ActiveWindow.SmallScroll ToRight:=6
Range("N2").Select
ActiveCell.FormulaR1C1 = "Stade"
Range("M2").Select
ActiveCell.FormulaR1C1 = "Nb CD"
Range("L2").Select
ActiveCell.FormulaR1C1 = "SUP"
Range("P2:R2").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("2:2").Select
Range("G2").Activate
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("P:R").Select
Selection.ColumnWidth = 6.43
Rows("2:2").RowHeight = 43.5
Rows("2:2").RowHeight = 54
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
Columns("Q:Q").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Columns("S:S").Select
Selection.Columns.Group
ActiveWindow.SmallScroll ToRight:=14
Columns("V:Y").Select
Selection.Columns.Group
ActiveWindow.SmallScroll ToRight:=2
Columns("AA:AA").Select
Selection.Columns.Group
Columns("AA:AB").Select
Selection.Columns.Group
Selection.Columns.Ungroup
Selection.Columns.Ungroup
Selection.Columns.Group
Columns("AD:AD").Select
Selection.Columns.Group
Columns("AF:AF").Select
Selection.Columns.Group
Columns("AH:AJ").Select
Selection.Columns.Group
ActiveWindow.SmallScroll ToRight:=6
Range("AD3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],tarif!R[-2]C[-29]:R[314]C[-28],2,FALSE)"
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC29,tarif!R1C1:R317C2,2,FALSE)"
Range("AD3").Select
Selection.AutoFill Destination:=Range("AD3:AD1142")
Range("AD3:AD1142").Select
Range("AG3").Select
ActiveCell.FormulaR1C1 = "=(RC[-3]-RC[-1])/RC[-3]"
Range("AG3").Select
Selection.Style = "Percent"
Selection.AutoFill Destination:=Range("AG3:AG1142")
Range("AG3:AG1142").Select
ActiveWindow.SmallScroll ToRight:=5
Sheets("enteteventes").Select
Selection.Copy
Sheets("collerleref").Select
Range("AM1").Select
ActiveSheet.Paste
Sheets("stocks").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("collerleref").Select
Range("BE2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("entetelots").Select
Selection.Copy
Sheets("collerleref").Select
Range("BI1").Select
ActiveSheet.Paste
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
Columns("A:A").Select
Application.CutCopyMode = False
Selection.FormatConditions.Add Type:=xlTextString, String:="italie", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="ims", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll ToRight:=4
Columns("M:M").Select
Selection.NumberFormat = "0"
Selection.TextToColumns Destination:=Range("M1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=1"
Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16751204
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10284031
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("N:N").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="BLOCAGE", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="SUPP", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 6684927
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="ANNULE", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColor = 26367
.ColorIndex = xlAutomatic
.PatternTintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="REMPLAC", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16751204
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10284031
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="SAE", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.Color = -16750849
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.Color = -16750849
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Color = -16750849
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Color = -16750849
.TintAndShade = 0
.Weight = xlThin
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("O:O").Select
Selection.Insert Shift:=xlToRight
Range("O3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""SAE"",""STOCK?"","""")"
Range("O3").Select
ActiveCell.FormulaR1C1 = "=IF(RC14=""SAE"",""STOCK?"","""")"
Range("O3").Select
Selection.AutoFill Destination:=Range("O3:O1142")
Range("O3:O1142").Select
Range("O2").Select
ActiveCell.FormulaR1C1 = "STADE2"
Columns("O:O").Select
Selection.Columns.Group
ActiveWindow.SmallScroll ToRight:=16
Range("AD2").Select
ActiveCell.FormulaR1C1 = "Tarif de référence AVANT"
Range("AD3").Select
Columns("AD:AE").Select
Selection.Copy
Columns("AL:AL").Select
Selection.Insert Shift:=xlToRight
Range("AL2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Tarif de référence APRES"
Columns("AL:AL").Select
Selection.Insert Shift:=xlToRight
Selection.Columns.Ungroup
Range("AL2").Select
ActiveCell.FormulaR1C1 = "PB DIF PRIX REF ET FACT"
Range("AL3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-8]=RC[1],"""",""VOIR SI AUTRE OP"")"
Range("AL3").Select
ActiveCell.FormulaR1C1 = "=IF(RC30=RC39,"""",""VOIR SI AUTRE OP"")"
Range("AL3").Select
With Selection.Font
.Color = -10092289
.TintAndShade = 0
End With
Selection.AutoFill Destination:=Range("AL3:AL1142")
Range("AL3:AL1142").Select
Range("AP6").Select
Selection.CurrentRegion.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
Rows("1:1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveWindow.SmallScroll ToRight:=4
Range("G3").Select
ActiveCell.FormulaR1C1 = "1"
Range("G4").Select
ActiveCell.FormulaR1C1 = "2"
Range("G3:G4").Select
Selection.AutoFill Destination:=Range("G3:G1142")
Range("G3:G1142").Select
ActiveWindow.SmallScroll ToRight:=10
Range("U3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Font.Bold = True
Range("V3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Font.Italic = True
Columns("AI:AI").Select
With Selection.Font
.Color = -11489280
.TintAndShade = 0
End With
Columns("AD:AH").Select
Selection.ColumnWidth = 8
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveWindow.SmallScroll ToRight:=7
Columns("AJ:AN").Select
Selection.ColumnWidth = 8
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("AO:AO").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("AQ:BQ").Select
Selection.Columns.Group
Columns("BQ:BQ").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("BQ2").Select
ActiveCell.FormulaR1C1 = "1"
Range("BR2").Select
ActiveCell.FormulaR1C1 = "2"
Range("BQ2:BR2").Select
Selection.AutoFill Destination:=Range("BQ2:CH2"), Type:=xlFillDefault
Range("BQ2:CH2").Select
Columns("BQ:CH").Select
Range("CH1").Activate
Columns("BQ:CH").EntireColumn.AutoFit
Range("CA2").Select
ActiveWindow.SmallScroll ToRight:=20
Columns("AI:AI").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=1"
Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll ToRight:=-11
Cells.Select
Range("J1").Activate
Selection.ColumnWidth = 11.29
Selection.ColumnWidth = 11
Rows("2:2").Select
Range("J2").Activate
Selection.AutoFilter
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
Columns("Q:S").Select
Selection.ColumnWidth = 5
Selection.ColumnWidth = 6
Columns("G:I").Select
Columns("G:I").EntireColumn.AutoFit
Columns("K:K").Select
Selection.Columns.Group
Columns("L:L").Select
Columns("L:L").EntireColumn.AutoFit
Columns("M:M").Select
Columns("M:M").EntireColumn.AutoFit
Columns("N:N").Select
Columns("N:N").EntireColumn.AutoFit
Columns("M:P").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("J4").Select
Columns("P:P").EntireColumn.AutoFit
Columns("P:P").ColumnWidth = 8.86
Columns("M:M").ColumnWidth = 5
Range("L4").Select
Columns("M:M").ColumnWidth = 4.14
Range("R7").Select
ActiveWindow.SmallScroll ToRight:=1
Columns("V:V").ColumnWidth = 16.14
ActiveWindow.SmallScroll ToRight:=5
Columns("AD:AD").Select
Selection.ColumnWidth = 8
Columns("AF:AF").Select
Selection.ColumnWidth = 8
Columns("AH:AH").Select
Selection.ColumnWidth = 8
Columns("AF:AF").Select
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
Columns("AH:AH").Select
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
Columns("AO:AO").Select
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
Columns("AP:AP").Select
Selection.Columns.Group
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
Columns("AM:AM").Select
Selection.ColumnWidth = 8
Columns("AO:AO").Select
Selection.Insert Shift:=xlToRight
Selection.Columns.Ungroup
Range("AO2").Select
ActiveCell.FormulaR1C1 = "AC"
Columns("AO:AO").Select
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
Columns("AO:AO").EntireColumn.AutoFit
Selection.ColumnWidth = 5.86
Range("AO3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-11]=RC[-2],"""",""AC"")"
Range("AO3").Select
Selection.AutoFill Destination:=Range("AO3:AO1142")
Range("AO3:AO1142").Select
Range("AP1143").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1140]C:R[-1]C)"
Range("AP1144").Select
Range("AR3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],originehorsitalie!R[1]C[-42]:R[9997]C[-38],3,FALSE)"
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Selection.AutoFill Destination:=Range("AR3:AU3"), Type:=xlFillDefault
Range("AR3:AU3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC43,originehorsitalie!R4C2:R10000C6,3,F ALSE)"
Range("AR3:AU3").Select
Range("AS3").Activate
Selection.FillRight
Range("AS3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC43,originehorsitalie!R4C2:R10000C6,4,F ALSE)"
Range("AT3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC43,originehorsitalie!R4C2:R10000C6,5,F ALSE)"
Range("AU3").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-3]"
Range("AU3").Select
Selection.Style = "Percent"
Range("AS3").Select
Range("AR3:AU3").Select
Selection.AutoFill Destination:=Range("AR3:AU1142")
Range("AR3:AU1142").Select
Range("AV3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],'12m'!R[2]C[-47]:R[9997]C[-42],4,FALSE)"
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,'12m'!R5C1:R10000C6,4,FALSE)"
Range("AV3").Select
Selection.AutoFill Destination:=Range("AV3:AY3"), Type:=xlFillDefault
Range("AV3:AY3").Select
Range("AW3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,'12m'!R5C1:R10000C6,5,FALSE)"
Range("AX3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,'12m'!R5C1:R10000C6,6,FALSE)"
Range("AY3").Select
ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-3]"
Range("AY3").Select
Selection.Style = "Percent"
Range("AV3:AY3").Select
Selection.AutoFill Destination:=Range("AV3:AY1142")
Range("AV3:AY1142").Select
ActiveWindow.SmallScroll ToRight:=9
Range("AZ3").Select
ActiveWindow.SmallScroll ToRight:=-2
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-9],hyp!R[2]C[-50]:R[9997]C[-45],4,FALSE)"
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,hyp!R5C2:R10000C7,4,FALSE)"
Range("AZ3").Select
Selection.AutoFill Destination:=Range("AZ3:BB3"), Type:=xlFillDefault
Range("AZ3:BB3").Select
Range("BA3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,hyp!R5C2:R10000C7,5,FALSE)"
Range("BB3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,hyp!R5C2:R10000C7,6,FALSE)"
Range("BC3").Select
ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-3]"
Range("BD3").Select
ActiveCell.FormulaR1C1 = "=RC[-4]/RC[-8]"
Range("BC3").Select
Selection.Style = "Percent"
Range("BD3").Select
Selection.Style = "Percent"
Range("AZ3:BB3").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Range("AZ3:BD3").Select
Selection.AutoFill Destination:=Range("AZ3:BD1142")
Range("AZ3:BD1142").Select
ActiveWindow.SmallScroll ToRight:=7
Range("BE3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,spes!R5C2:R10000C7,4,FALSE)"
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Selection.AutoFill Destination:=Range("BE3:BG3"), Type:=xlFillDefault
Range("BE3:BG3").Select
Range("BF3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,spes!R5C2:R10000C7,5,FALSE)"
Range("BG3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,spes!R5C2:R10000C7,6,FALSE)"
Range("BH3").Select
ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-3]"
Range("BI3").Select
ActiveCell.FormulaR1C1 = "=RC[-4]/RC[-13]"
Range("BH3:BI3").Select
Selection.Style = "Percent"
Range("BE3:BI3").Select
Selection.AutoFill Destination:=Range("BE3:BI1142")
Range("BE3:BI1142").Select
Range("BJ3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-19],stocks!R[3]C[-58]:R[1048573]C[-32],26,FALSE)"
ActiveWindow.SmallScroll ToRight:=7
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,26,FALSE) "
Range("BJ3").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Selection.AutoFill Destination:=Range("BJ3:BM3"), Type:=xlFillDefault
Range("BJ3:BM3").Select
Range("BK3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,27,FALSE) "
Range("BL3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,28,FALSE) "
Range("BJ3").Select
Sheets("stocks").Select
Sheets("collerleref").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,23,FALSE) "
Range("BK3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,24,FALSE) "
Range("BL3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,25,FALSE) "
Range("BJ3:BM3").Select
Selection.AutoFill Destination:=Range("BJ3:BM1142")
Range("BJ3:BM1142").Select
Columns("BJ:BM").Select
Selection.ColumnWidth = 8
Range("BM2").Select
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
Range("G2").Select
End Sub
Après, sans support et sans savoir ce qu'est censée faire cette procédure, je passe la main.


Bon courage.


ROGER2327
#6829

Vendredi 13 Absolu 141 (Saint Cantarel, l’illuminateur - fête Suprême Quarte)
4ème Sanculottide An CCXXI, 5,8279h - fête de l'Opinion
2013-W38-5T13:59:13Z
 
Vote positif 0 Vote négatif
phlaurent55

phlaurent55

Nous a quittés en 2020
Repose en paix
Re : Macro trop longue

Bonjour à tous,

pris au hasard dans ce code:

Range("AO3:AO1142").Select
Range("AP1143").Select

pour ces deux lignes qui se suivent dans le code, la première n'est pas nécessaire

même réflexion pour ce qui suit

Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"

L'enregistreur de macro est utile mais il faut éviter de "bégayer" dans les manipulations sous peine de les retrouver dans le code généré

à+
Philippe

 
Vote positif 0 Vote négatif
T

Tirou

XLDnaute Occasionnel
Re : Macro trop longue

Bon, c'est vraiment fastidieux, voilà un peu avec une première partie un peu épurée.
Si tu veux d'avantage d'aide (de ma part en tout cas) poste nous 2 fichiers, l'un avec ce que tu as en entrée, 2 : ce que tu veux en sortie. (retires les données sensibles) Je pense que ce sera plus rapide de repartir de la base que de se plonger dans ton bloc de béton.


(ps : application.screenupdating = false devrait bien t'aider déjà, vu le nombre de selects de partout ... )

Code: 
Sub bw_vers_excel()
'
' bw_vers_excel Macro
' mise en forme suite à l'export ajout des entente lots + ventes + stocks
'
' Touche de raccourci du clavier: Ctrl+w
'
    Application.ScreenUpdating = False
    
    Cells.ClearFormats
    With Cells.Font
        .Name = "Segoe UI"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
        End With
    Rows("4:4").Delete Shift:=xlUp
    Rows("1:1").Delete Shift:=xlUp
    Columns("D").Delete Shift:=xlToLeft
    Columns("F:F").Delete Shift:=xlToLeft
    
    Columns("AA:AB").Select
    Range("AB1").Activate
    Selection.Delete Shift:=xlToLeft
    
    Columns("C:C").Cut
    Columns("A:A").Insert Shift:=xlToRight
    Columns("B:B").Cut
    Columns("D").Insert Shift:=xlToRight
    Columns("E:G").Cut
    Columns("B:B").Insert Shift:=xlToRight
    Columns("H:I").Cut
    Columns("B:B").Insert Shift:=xlToRight
    Columns("N:N").Cut
    Columns("P:P").Insert Shift:=xlToRight
    Columns("O:Q").Cut
    Columns("D").Insert Shift:=xlToRight
    Range("G2").FormulaR1C1 = "REF13 - UPC"
    Range("I2").FormulaR1C1 = "1ère com"
    Range("L2").FormulaR1C1 = "Désignation"
    Columns("S:T").Cut
    Columns("R:R").Insert Shift:=xlToRight
    Columns("R:T").Cut
    Columns("I:I").Insert Shift:=xlToRight

    With Columns("G:G")
        .Insert Shift:=xlToRight
        .Insert Shift:=xlToRight
        .Insert Shift:=xlToRight
        End With
    Range("G2").FormulaR1C1 = "NUM1"
    Range("H2").FormulaR1C1 = "NUM2"
    Range("I2").FormulaR1C1 = "GENRE"
    
    With Columns("P:P")
        .Insert Shift:=xlToRight
        .Insert Shift:=xlToRight
        .Insert Shift:=xlToRight
        End With
        
    Range("P2").FormulaR1C1 = "RAFRAICH"
    Range("Q2").FormulaR1C1 = "VTE ORIGINE (mep)"
    Range("R2").FormulaR1C1 = "LOTS?"

    Columns("AD:AD").Insert Shift:=xlToRight
    Range("AD2").FormulaR1C1 = "Valeur"
    Columns("AE:AF").Insert Shift:=xlToRight
    Range("AE2").FormulaR1C1 = "TARIF OS"
    Range("AF2").FormulaR1C1 = "VALEUR"
    Columns("AG:AG").Insert Shift:=xlToRight
    Range("AG2").FormulaR1C1 = "BAISSE"
    
    With Columns("AJ:AJ")
        .Style = "Comma"
        .Cut
        End With
        
    Columns("AJ:AJ").Select
    Application.CutCopyMode = False
    Selection.Cut
    Columns("AH:AH").Insert Shift:=xlToRight
    Range("AK2").FormulaR1C1 = "ESTIMATE OBJ/REF"
    Cells.Select
    Range("X1").Activate
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        End With
        
    Columns("J:J").Copy
    Columns("AL:AL").Select
    ActiveSheet.Paste
    
    Cells.EntireColumn.AutoFit
    
    Columns("A:F").Select
    Application.CutCopyMode = False
    Selection.Columns.Group
    ActiveWindow.SmallScroll ToRight:=6
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "Stade"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "Nb CD"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "SUP"
    Range("P2:R2").Select
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 90
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Rows("2:2").Select
    Range("G2").Activate
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Columns("P:R").Select
    Selection.ColumnWidth = 6.43
    Rows("2:2").RowHeight = 43.5
    Rows("2:2").RowHeight = 54
    With Selection.Font
    .Color = -4165632
    .TintAndShade = 0
    End With
    Columns("Q:Q").Select
    Selection.Style = "Comma"
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    Columns("S:S").Select
    Selection.Columns.Group
    Columns("V:Y").Select
    Selection.Columns.Group
    Columns("AA:AA").Select
    Selection.Columns.Group
    Columns("AA:AB").Select
    Selection.Columns.Group
    Selection.Columns.Ungroup
    Selection.Columns.Ungroup
    Selection.Columns.Group
    Columns("AD:AD").Select
    Selection.Columns.Group
    Columns("AF:AF").Select
    Selection.Columns.Group
    Columns("AH:AJ").Select
    Selection.Columns.Group
    Range("AD3").Select
    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-1],tarif!R[-2]C[-29]:R[314]C[-28],2,FALSE)"
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC29,tarif!R1C1:R317C2,2,FALSE)"
    Range("AD3").Select
    Selection.AutoFill Destination:=Range("AD3:AD1142")
    Range("AD3:AD1142").Select
    Range("AG3").Select
    ActiveCell.FormulaR1C1 = "=(RC[-3]-RC[-1])/RC[-3]"
    Range("AG3").Select
    Selection.Style = "Percent"
    Selection.AutoFill Destination:=Range("AG3:AG1142")
    Range("AG3:AG1142").Select
    ActiveWindow.SmallScroll ToRight:=5
    Sheets("enteteventes").Select
    Selection.Copy
    Sheets("collerleref").Select
    Range("AM1").Select
    ActiveSheet.Paste
    Sheets("stocks").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("collerleref").Select
    Range("BE2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    With Selection
    .HorizontalAlignment = xlRight
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Sheets("entetelots").Select
    Selection.Copy
    Sheets("collerleref").Select
    Range("BI1").Select
    ActiveSheet.Paste
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.FormatConditions.Add Type:=xlTextString, String:="italie", _
    TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
    .Color = -16383844
    .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 13551615
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlTextString, String:="ims", _
    TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
    .Color = -16752384
    .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 13561798
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    ActiveWindow.SmallScroll ToRight:=4
    Columns("M:M").Select
    Selection.NumberFormat = "0"
    Selection.TextToColumns Destination:=Range("M1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
    Formula1:="=1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
    .Color = -16751204
    .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 10284031
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Columns("N:N").Select
    Selection.FormatConditions.Add Type:=xlTextString, String:="BLOCAGE", _
    TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
    .Color = -16383844
    .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 13551615
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlTextString, String:="SUPP", _
    TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 6684927
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlTextString, String:="ANNULE", _
    TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
    .PatternColor = 26367
    .ColorIndex = xlAutomatic
    .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlTextString, String:="REMPLAC", _
    TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
    .Color = -16751204
    .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 10284031
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlTextString, String:="SAE", _
    TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Borders(xlLeft)
    .LineStyle = xlContinuous
    .Color = -16750849
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
    .LineStyle = xlContinuous
    .Color = -16750849
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
    .LineStyle = xlContinuous
    .Color = -16750849
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
    .LineStyle = xlContinuous
    .Color = -16750849
    .TintAndShade = 0
    .Weight = xlThin
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Columns("O:O").Select
    Selection.Insert Shift:=xlToRight
    Range("O3").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""SAE"",""STOCK?"","""")"
    Range("O3").Select
    ActiveCell.FormulaR1C1 = "=IF(RC14=""SAE"",""STOCK?"","""")"
    Range("O3").Select
    Selection.AutoFill Destination:=Range("O3:O1142")
    Range("O3:O1142").Select
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "STADE2"
    Columns("O:O").Select
    Selection.Columns.Group
    ActiveWindow.SmallScroll ToRight:=16
    Range("AD2").Select
    ActiveCell.FormulaR1C1 = "Tarif de référence AVANT"
    Range("AD3").Select
    Columns("AD:AE").Select
    Selection.Copy
    Columns("AL:AL").Select
    Selection.Insert Shift:=xlToRight
    Range("AL2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Tarif de référence APRES"
    Columns("AL:AL").Select
    Selection.Insert Shift:=xlToRight
    Selection.Columns.Ungroup
    Range("AL2").Select
    ActiveCell.FormulaR1C1 = "PB DIF PRIX REF ET FACT"
    Range("AL3").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-8]=RC[1],"""",""VOIR SI AUTRE OP"")"
    Range("AL3").Select
    ActiveCell.FormulaR1C1 = "=IF(RC30=RC39,"""",""VOIR SI AUTRE OP"")"
    Range("AL3").Select
    With Selection.Font
    .Color = -10092289
    .TintAndShade = 0
    End With
    Selection.AutoFill Destination:=Range("AL3:AL1142")
    Range("AL3:AL1142").Select
    Range("AP6").Select
    Selection.CurrentRegion.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlHairline
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlHairline
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlHairline
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlHairline
    End With
    With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlHairline
    End With
    With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlHairline
    End With
    Rows("1:1").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveWindow.SmallScroll ToRight:=4
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("G4").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("G3:G4").Select
    Selection.AutoFill Destination:=Range("G3:G1142")
    Range("G3:G1142").Select
    ActiveWindow.SmallScroll ToRight:=10
    Range("U3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Font.Bold = True
    Range("V3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Font.Italic = True
    Columns("AI:AI").Select
    With Selection.Font
    .Color = -11489280
    .TintAndShade = 0
    End With
    Columns("AD:AH").Select
    Selection.ColumnWidth = 8
    With Selection
    .HorizontalAlignment = xlRight
    .VerticalAlignment = xlCenter
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    ActiveWindow.SmallScroll ToRight:=7
    Columns("AJ:AN").Select
    Selection.ColumnWidth = 8
    With Selection
    .HorizontalAlignment = xlRight
    .VerticalAlignment = xlCenter
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Columns("AO:AO").Select
    Selection.Style = "Comma"
    Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    With Selection
    .HorizontalAlignment = xlRight
    .VerticalAlignment = xlCenter
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Columns("AQ:BQ").Select
    Selection.Columns.Group
    Columns("BQ:BQ").Select
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Range("BQ2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("BR2").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("BQ2:BR2").Select
    Selection.AutoFill Destination:=Range("BQ2:CH2"), Type:=xlFillDefault
    Range("BQ2:CH2").Select
    Columns("BQ:CH").Select
    Range("CH1").Activate
    Columns("BQ:CH").EntireColumn.AutoFit
    Range("CA2").Select
    ActiveWindow.SmallScroll ToRight:=20
    Columns("AI:AI").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
    Formula1:="=1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
    .Color = -16752384
    .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 13561798
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    ActiveWindow.SmallScroll ToRight:=-11
    Cells.Select
    Range("J1").Activate
    Selection.ColumnWidth = 11.29
    Selection.ColumnWidth = 11
    Rows("2:2").Select
    Range("J2").Activate
    Selection.AutoFilter
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
    Columns("Q:S").Select
    Selection.ColumnWidth = 5
    Selection.ColumnWidth = 6
    Columns("G:I").Select
    Columns("G:I").EntireColumn.AutoFit
    Columns("K:K").Select
    Selection.Columns.Group
    Columns("L:L").Select
    Columns("L:L").EntireColumn.AutoFit
    Columns("M:M").Select
    Columns("M:M").EntireColumn.AutoFit
    Columns("N:N").Select
    Columns("N:N").EntireColumn.AutoFit
    Columns("M:P").Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Range("J4").Select
    Columns("P:P").EntireColumn.AutoFit
    Columns("P:P").ColumnWidth = 8.86
    Columns("M:M").ColumnWidth = 5
    Range("L4").Select
    Columns("M:M").ColumnWidth = 4.14
    Range("R7").Select
    ActiveWindow.SmallScroll ToRight:=1
    Columns("V:V").ColumnWidth = 16.14
    ActiveWindow.SmallScroll ToRight:=5
    Columns("AD:AD").Select
    Selection.ColumnWidth = 8
    Columns("AF:AF").Select
    Selection.ColumnWidth = 8
    Columns("AH:AH").Select
    Selection.ColumnWidth = 8
    Columns("AF:AF").Select
    With Selection.Font
    .Color = -4165632
    .TintAndShade = 0
    End With
    Columns("AH:AH").Select
    With Selection.Font
    .Color = -4165632
    .TintAndShade = 0
    End With
    Columns("AO:AO").Select
    With Selection.Font
    .Color = -4165632
    .TintAndShade = 0
    End With
    Columns("AP:AP").Select
    Selection.Columns.Group
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
    Columns("AM:AM").Select
    Selection.ColumnWidth = 8
    Columns("AO:AO").Select
    Selection.Insert Shift:=xlToRight
    Selection.Columns.Ungroup
    Range("AO2").Select
    ActiveCell.FormulaR1C1 = "AC"
    Columns("AO:AO").Select
    With Selection.Font
    .Color = -4165632
    .TintAndShade = 0
    End With
    Columns("AO:AO").EntireColumn.AutoFit
    Selection.ColumnWidth = 5.86
    Range("AO3").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-11]=RC[-2],"""",""AC"")"
    Range("AO3").Select
    Selection.AutoFill Destination:=Range("AO3:AO1142")
    Range("AO3:AO1142").Select
    Range("AP1143").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-1140]C:R[-1]C)"
    Range("AP1144").Select
    Range("AR3").Select
    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-1],originehorsitalie!R[1]C[-42]:R[9997]C[-38],3,FALSE)"
    Selection.Style = "Comma"
    Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    Selection.AutoFill Destination:=Range("AR3:AU3"), Type:=xlFillDefault
    Range("AR3:AU3").Select
    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC43,originehorsitalie!R4C2:R10000C6,3,F ALSE)"
    Range("AR3:AU3").Select
    Range("AS3").Activate
    Selection.FillRight
    Range("AS3").Select
    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC43,originehorsitalie!R4C2:R10000C6,4,F ALSE)"
    Range("AT3").Select
    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC43,originehorsitalie!R4C2:R10000C6,5,F ALSE)"
    Range("AU3").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-3]"
    Range("AU3").Select
    Selection.Style = "Percent"
    Range("AS3").Select
    Range("AR3:AU3").Select
    Selection.AutoFill Destination:=Range("AR3:AU1142")
    Range("AR3:AU1142").Select
    Range("AV3").Select
    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-5],'12m'!R[2]C[-47]:R[9997]C[-42],4,FALSE)"
    Selection.Style = "Comma"
    Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,'12m'!R5C1:R10000C6,4,FALSE)"
    Range("AV3").Select
    Selection.AutoFill Destination:=Range("AV3:AY3"), Type:=xlFillDefault
    Range("AV3:AY3").Select
    Range("AW3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,'12m'!R5C1:R10000C6,5,FALSE)"
    Range("AX3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,'12m'!R5C1:R10000C6,6,FALSE)"
    Range("AY3").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-3]"
    Range("AY3").Select
    Selection.Style = "Percent"
    Range("AV3:AY3").Select
    Selection.AutoFill Destination:=Range("AV3:AY1142")
    Range("AV3:AY1142").Select
    ActiveWindow.SmallScroll ToRight:=9
    Range("AZ3").Select
    ActiveWindow.SmallScroll ToRight:=-2
    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-9],hyp!R[2]C[-50]:R[9997]C[-45],4,FALSE)"
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,hyp!R5C2:R10000C7,4,FALSE)"
    Range("AZ3").Select
    Selection.AutoFill Destination:=Range("AZ3:BB3"), Type:=xlFillDefault
    Range("AZ3:BB3").Select
    Range("BA3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,hyp!R5C2:R10000C7,5,FALSE)"
    Range("BB3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,hyp!R5C2:R10000C7,6,FALSE)"
    Range("BC3").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-3]"
    Range("BD3").Select
    ActiveCell.FormulaR1C1 = "=RC[-4]/RC[-8]"
    Range("BC3").Select
    Selection.Style = "Percent"
    Range("BD3").Select
    Selection.Style = "Percent"
    Range("AZ3:BB3").Select
    Selection.Style = "Comma"
    Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    Range("AZ3:BD3").Select
    Selection.AutoFill Destination:=Range("AZ3:BD1142")
    Range("AZ3:BD1142").Select
    ActiveWindow.SmallScroll ToRight:=7
    Range("BE3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,spes!R5C2:R10000C7,4,FALSE)"
    Selection.Style = "Comma"
    Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    Selection.AutoFill Destination:=Range("BE3:BG3"), Type:=xlFillDefault
    Range("BE3:BG3").Select
    Range("BF3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,spes!R5C2:R10000C7,5,FALSE)"
    Range("BG3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,spes!R5C2:R10000C7,6,FALSE)"
    Range("BH3").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-3]"
    Range("BI3").Select
    ActiveCell.FormulaR1C1 = "=RC[-4]/RC[-13]"
    Range("BH3:BI3").Select
    Selection.Style = "Percent"
    Range("BE3:BI3").Select
    Selection.AutoFill Destination:=Range("BE3:BI1142")
    Range("BE3:BI1142").Select
    Range("BJ3").Select
    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-19],stocks!R[3]C[-58]:R[1048573]C[-32],26,FALSE)"
    ActiveWindow.SmallScroll ToRight:=7
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,26,FALSE) "
    Range("BJ3").Select
    Selection.Style = "Comma"
    Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    Selection.AutoFill Destination:=Range("BJ3:BM3"), Type:=xlFillDefault
    Range("BJ3:BM3").Select
    Range("BK3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,27,FALSE) "
    Range("BL3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,28,FALSE) "
    Range("BJ3").Select
    Sheets("stocks").Select
    Sheets("collerleref").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,23,FALSE) "
    Range("BK3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,24,FALSE) "
    Range("BL3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC43,stocks!R6C4:R10000C30,25,FALSE) "
    Range("BJ3:BM3").Select
    Selection.AutoFill Destination:=Range("BJ3:BM1142")
    Range("BJ3:BM1142").Select
    Columns("BJ:BM").Select
    Selection.ColumnWidth = 8
    Range("BM2").Select
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
    Range("G2").Select
End Sub
 
Dernière édition:
Vote positif 0 Vote négatif

Discussions similaires

F
  • Résolu(e)
XL 2021 Créer Macro permettant d'indexer des nouveaux clients ? [Résolu]
4 5 6
Réponses
82
Affichages
3 K
fanou06
F
L
  • Question
XL 2016 Macro et suppressions de lignes
Réponses
9
Affichages
763
fanfan38
fanfan38
M
  • Question
Microsoft 365 deux macros succesives et un bouton
2
Réponses
16
Affichages
608
Phil69970
Phil69970
R
  • Question
Microsoft 365 [RESOLU] Mail multi destinataires avec vba
Réponses
11
Affichages
1 K
romubzh35
R
R
  • Question
Microsoft 365 Copie ecran Userform pour envoi par mail [RESOLU]
2
Réponses
19
Affichages
1 K
patricktoulon
patricktoulon
Philou0607
  • Question
XL 2016 Erreur 1004 - Erreur définie par l'application ou par l'objet
Réponses
4
Affichages
686
Philou0607
Philou0607
Usine à gaz
  • Résolu(e)
XL 2021 recherche supprimer les espaces '"si valeur cherchée est numérique" sinon NON
Réponses
7
Affichages
509
Usine à gaz
Usine à gaz
J
  • Question
XL 2019 Borné une extraction par date
2
Réponses
22
Affichages
2 K
jui42
J
Nicolas JACQUIN
  • Question
XL 2021 Exportation feuille msgbox yes no avec module Thisworkbook
2
Réponses
21
Affichages
831
Nicolas JACQUIN
Nicolas JACQUIN
S
  • Question
Microsoft 365 comparaison texte
Réponses
5
Affichages
628
svn
S

Membres actuellement en ligne

16 membres et 295 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
314 422
Messages
2 109 447
Membres
110 482
dernier inscrit
ilyxxxh