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
|
sub h()
For i = reason_row_item + 1 To 65000
Windows("a.xls").Activate
Sheets("Pivot only").Select
temporaire = 0
If (Sheets("Pivot only").Range(reason_Column_item & i).Value <> "Grand Total") Then
constraint = Sheets("Pivot only").Range(reason_Column_item & i).Value
quantity = Sheets("Pivot only").Range(GT_Column_item & i).Value
Windows("b.xls").Activate
Sheets("Sheet1").Select
j = 3
While Not (Cells(j, 5) = "Grand total")
If Cells(j, 3) = constraint Then
Range("A" & j & ":G" & j).Font.ColorIndex = 3
Range("E" & j & ":G" & j).Borders.LineStyle = xlContinuous
Cells(j, 7).Value = quantity
temporaire = 1
End If
j = j + 1
Wend
If temporaire = 0 Then
y = 3
While Not IsEmpty(Cells(y, 6))
y = y + 1
Wend
Cells(y, 3) = constraint
Cells(y, 4) = Workbooks("a.xls").Sheets("Pivot only").Cells(i, Desc_Column_item)
Cells(y, 6) = quantity
Range("A" & y & ":G" & y).Font.ColorIndex = 3
Range("A" & y & ":G" & y).Borders.LineStyle = xlContinuous
End If
Else: i = 70000
Windows("b.xls").Activate
Sheets("Sheet1").Select
Cells(1, 7).Value = "Total Esca"
Cells(1, 8).Value = "=today()"
End If
Next i
end sub |
Partager