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
| Sub Export()
Dim Ligne As Long, Plage As Range, Ligne1 As Long, Ligne2 As Long
Dim Wbk As Workbook, Sh1 As Worksheet, Sh2 As Worksheet, C As Range
Dim NomProjet As String
With ThisWorkbook.Sheets("Feuil1")
Ligne = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
Set Plage = .Range(.[A4], .Cells(Ligne, 1))
Ligne1 = 1
Ligne2 = 1
End With
Set Wbk = Workbooks("Mon Nouveau Fichier.xlsm")
Set Sh1 = Wbk.Sheets("Elements d'obsolescence ")
Set Sh2 = Wbk.Sheets("Elements Greenwich")
For Each C In Plage
If Application.CountIf(C.Resize(, 7), "") < 7 Then
If C.Value <> "" Then NomProjet = C.Value
If C.Offset(, 2).Interior.ColorIndex = 3 Or _
C.Offset(, 4).Interior.ColorIndex = 3 Then
Ligne2 = Ligne2 + 3
Sh2.Cells(Ligne2, 1) = NomProjet
Sh2.Cells(Ligne2, 2).Resize(, 6).Value = C.Offset(, 1).Resize(, 6).Value
Else
Ligne1 = Ligne1 + 3
Sh1.Cells(Ligne1, 1) = NomProjet
Sh1.Cells(Ligne1, 2).Resize(, 6).Value = C.Offset(, 1).Resize(, 6).Value
End If
End If
Next C
End Sub |
Partager