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
|
Sub Création_Devis()
Dim Prest As Worksheet
Dim Devis As Worksheet
Dim Pr As Range
Dim Dev As Range
Dim i As Integer
Dim j As Integer
Set Prest = ThisWorkbook.Sheets("PRESTATAIRES")
Set Devis = ThisWorkbook.Sheets("DEVIS")
Set Pr = Prest.Range("C4")
Set Dev = Devis.Range("A5")
Pr = Pr.Offset(0)
Dev = Dev.Offset(0)
i = 0
j = 0
Do While Pr.Offset(i, -2) <> ""
If Pr.Offset(i, 0) = True Then
Dev.Offset(j, 0).Value = Pr.Offset(i, -2).Value
End If
i = i + 1
If Dev.Offset(j, 0) = "" Then
j = j
Else
j = j + 1
End If
Loop
Devis.Select
Dev.Offset(0, 0).Select
ActiveSheet.Range("$A$4:$C$8").RemoveDuplicates Columns:=1
Range("A5:C18").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
End With
Prest.Select
End Sub |
Partager