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
| Sub copiecellules()
Dim F1 As Worksheet
Dim F2 As Worksheet
Dim F3 As Worksheet
Dim F4 As Worksheet
Dim nb1 As Integer
Dim nb2 As Integer
Dim nb3 As Integer
Dim nb4 As Integer
Dim nb5 As Integer
Dim nb6 As Integer
Set F1 = Sheets("SP")
Set F2 = Sheets("fcst total")
Set F3 = Sheets("CVIT")
Set F4 = Sheets("PM")
F2.Select
Range("A2:A65536").Clear
Range("B2:B65536").Clear
Range("C2:A65536").Clear
Range("D2:B65536").Clear
Range("E2:A65536").Clear
Range("F2:B65536").Clear
F1.Select
nb1 = Cells(2, 2).End(xlDown).Row
MsgBox (nb1)
Range(Cells(nb1, 100), Cells(2, 1)).Select
Selection.Replace What:="", Replacement:="-", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range(Cells(nb1, 2), Cells(2, 2)).Copy _
Destination:=F2.Range("A2")
Range(Cells(nb1, 8), Cells(2, 8)).Copy _
Destination:=F2.Range("B2")
Range(Cells(nb1, 4), Cells(2, 4)).Copy _
Destination:=F2.Range("C2")
Range(Cells(nb1, 5), Cells(2, 5)).Copy _
Destination:=F2.Range("D2")
Range(Cells(nb1, 9), Cells(2, 9)).Copy _
Destination:=F2.Range("F2")
Range(Cells(nb1, 29), Cells(2, 29)).Select
Selection.Copy
F2.Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
nb2 = nb1 + 1
MsgBox (nb2)
F3.Select
nb3 = Cells(2, 2).End(xlDown).Row
Range(Cells(nb3, 100), Cells(2, 1)).Select
Selection.Replace What:="", Replacement:="-", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range(Cells(nb3, 10), Cells(2, 10)).Copy _
Destination:=F2.Range("A" & nb2)
Range(Cells(nb3, 1), Cells(2, 1)).Copy _
Destination:=F2.Range("B" & nb2)
Range(Cells(nb3, 3), Cells(2, 3)).Copy _
Destination:=F2.Range("C" & nb2)
Range(Cells(nb3, 2), Cells(2, 2)).Copy _
Destination:=F2.Range("D" & nb2)
Range(Cells(nb3, 79), Cells(2, 79)).Copy _
Destination:=F2.Range("F" & nb2)
Range(Cells(nb3, 18), Cells(2, 18)).Select
Selection.Copy
F2.Select
Range("E" & nb2).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
nb5 = nb1 + nb3
F4.Select
nb4 = Cells(2, 2).End(xlDown).Row
Range(Cells(nb4, 100), Cells(2, 1)).Select
Selection.Replace What:="", Replacement:="-", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range(Cells(nb4, 10), Cells(2, 10)).Copy _
Destination:=F2.Range("A" & nb5)
Range(Cells(nb4, 1), Cells(2, 1)).Copy _
Destination:=F2.Range("B" & nb5)
Range(Cells(nb4, 3), Cells(2, 3)).Copy _
Destination:=F2.Range("C" & nb5)
Range(Cells(nb4, 2), Cells(2, 2)).Copy _
Destination:=F2.Range("D" & nb5)
Range(Cells(nb4, 79), Cells(2, 79)).Copy _
Destination:=F2.Range("F" & nb5)
Range(Cells(nb4, 18), Cells(2, 18)).Select
Selection.Copy
F2.Select
Range("E" & nb5).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub |
Partager