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
| Sub Export()
Dim Ligne As Long, Plage As Range
Dim Wbk As Workbook, Sh1 As Worksheet, Sh2 As Worksheet, C As Range
Dim Res As Long, Teste As Boolean
With ThisWorkbook.Sheets("Feuil1")
Ligne = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
Set Plage = .Range(.[A4], .Cells(Ligne, 1))
Set Wbk = Workbooks("Mon Fichier Final.xlsm")
Set Sh1 = Wbk.Sheets("Elements d'obsolescence")
Set Sh2 = Wbk.Sheets("Elements Greenwich")
For Each C In Plage
If C.Row = 65536 Then Stop
If C.Value <> "" Then Res = C.Row
If C.Offset(, 2).Interior.ColorIndex = 3 Or _
C.Offset(, 4).Interior.ColorIndex = 3 Then
Teste = True
End If
If (Application.CountIf(C.Resize(, 7), "") = 7 And Res > 0) Or C.Row = Plage.Rows.Count + 3 Then
If Teste = True Then
Ligne = Sh1.Cells(Sh1.Rows.Count, 2).End(xlUp).Offset(2).Row
.Range(.Cells(Res, 1), C).Resize(, 7).Copy Sh1.Cells(Ligne, 1)
Else
Ligne = Sh2.Cells(Sh2.Rows.Count, 2).End(xlUp).Offset(2).Row
.Range(.Cells(Res, 1), C).Resize(, 7).Copy Sh2.Cells(Ligne, 1)
End If
Teste = False
End If
Next C
End With
End Sub |
Partager