1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
| Sub TRANSFORNSI()
Application.ScreenUpdating = False
Workbooks.Open Filename:="D:\03 - PREPARATION DE MISSION\RNSI.xls"
Cells.AutoFilter
ActiveWorkbook.Worksheets("RNSI").QueryTables(1).Sort.SortFields.Clear
ActiveWorkbook.Worksheets("RNSI").QueryTables(1).Sort.SortFields.Add Key:= _
Range("D1:D55214"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("RNSI").QueryTables(1).Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("D2").Copy
Windows("OUTILS WPT RNSI.xls").Activate
Range("N38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("RNSI.xls").Activate
Cells.AutoFilter
Range("A:A,B:B,G:G,M:M,N:N,T:T,U:U,V:V,W:W,Z:Z").Copy
Sheets.Add after:=Sheets(Sheets.Count)
Range("A1").Select
ActiveSheet.Paste
Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=x2FormatFromLeftOrAbove
Columns("G:G").Select
Application.DisplayAlerts = False
selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Application.DisplayAlerts = True
selection.Delete Shift:=xlToLeft
Columns("J:J").Insert Shift:=xlToRight, CopyOrigin:=x2FormatFromLeftOrAbove
Application.DisplayAlerts = False
Columns("I:I").TextToColumns Destination:=Range("I1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(8, 1)), TrailingMinusNumbers:=True
Application.DisplayAlerts = True
Columns("I:I").Delete Shift:=xlToLeft
Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=x2FormatFromLeftOrAbove
Application.DisplayAlerts = False
Columns("H:H").TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Application.DisplayAlerts = True
Columns("H:H").Delete Shift:=xlToLeft
Range("K2").FormulaR1C1 = "=CONCATENATE(C[-5],C[-4],C[-3],"" "",C[-2],"" "",C[-1])"
Range("K2").AutoFill Destination:=Range("K2:K65000")
Range("A:A,B:B,C:C,D:D,E:E,K:K").Copy
Sheets.Add after:=Sheets(Sheets.Count)
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("F:F").Select
Cells.Replace What:=" –", Replacement:="", lookat:=xlPart, searchorder _
:=xlByRows, MatchCase:=False, searchformat:=False, ReplaceFormat:=False
Cells.Replace What:=" /", Replacement:="", lookat:=xlPart, searchorder _
:=xlByRows, MatchCase:=False, searchformat:=False, ReplaceFormat:=False
Cells.Replace What:=",", Replacement:="", lookat:=xlPart, searchorder:= _
xlByRows, MatchCase:=False, searchformat:=False, ReplaceFormat:=False
Cells.Replace What:=" -", Replacement:="", lookat:=xlPart, searchorder _
:=xlByRows, MatchCase:=False, searchformat:=False, ReplaceFormat:=False
Cells.Replace What:="(", Replacement:="", lookat:=xlPart, searchorder:= _
xlByRows, MatchCase:=False, searchformat:=False, ReplaceFormat:=False
Cells.Replace What:=")", Replacement:="", lookat:=xlPart, searchorder:= _
xlByRows, MatchCase:=False, searchformat:=False, ReplaceFormat:=False
Cells.Replace What:="SOL-", Replacement:="SOL ", lookat:=xlPart, searchorder:= _
xlByRows, MatchCase:=False, searchformat:=False, ReplaceFormat:=False
Range("G2").FormulaR1C1 = "=CONCATENATE(C[-1],"" "",C[-6],"" "",C[-4],"" "",C[-5])"
Range("G2").AutoFill Destination:=Range("G2:G65000")
Range("G:G,C:C,D:D,E:E").Copy
Sheets.Add after:=Sheets(Sheets.Count)
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Feuil3").Select
Columns("A:A").Cut
Columns("E:E").Insert Shift:=xlToRight
Columns("A:D").Copy
Windows("OUTILS WPT RNSI.xls").Activate
Columns("A:D").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.DisplayAlerts = False
Windows("RNSI.xls").Close
Application.DisplayAlerts = True
Sheets("WORKS_SHEET").Select
Columns("D:D").ColumnWidth = 30
With selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:D").AutoFilter
Application.DisplayAlerts = False
ChDir "D:\03 - PREPARATION DE MISSION"
ActiveWorkbook.SaveAs Filename:= _
"D:\03 - PREPARATION DE MISSION\OUTILS WPT RNSI.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True
MsgBox ("Chargement RNSI terminé")
Application.ScreenUpdating = True
End Sub |
dégrossir macro visualbasic
Répondre avec citation
Partager