Excel: Copy a row to another worksheet if a certain cell is not empty

Question

i'm looking to use a marco that would be able to search a column in said sheet and if the cell is not empty (cell T), it copy that entire rows data/formatting and paste it into another sheet [sheet "genealogie" in my case] along with any other rows that contained a not empty cell.

https://i.stack.imgur.com/ISRcD.png

I tried this code but unfortunately it copies all the lines even when the cell in question (column T) is empty.

Sub Copyl()
Dim Cell As Range
With Sheets("Tableur")

    For Each Cell In .Range("B7:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
        If Not IsEmpty(Range("T7:T" & .Cells(.Rows.Count, "T").End(xlUp).Row).Value) Then

            .Rows(Cell.Row).Copy Destination:=Sheets("genealogie").Rows(Cell.Row)

        End If
    Next Cell

End With
End Sub

Thanks by advance....

If Not IsEmpty(.Range("T" & Cell.Row)) Then. Or just use Range.AutoFilter and avoid looping. — BigBen
Dim Cell As Range - Cell is a keyword so you might avoid future problems to get in the habit of using a different name — JohnnieL
@JohnnieL - that's a common misconception. Cells is a member of the Excel object model. Cell is not. i.stack.imgur.com/CfDlh.png — BigBen

SevC_10 SevC_10 · Accepted Answer · 2021-02-10T14:38:57

You check the entire range in the T column: just check the single cell in the corresponding row

Sub Copyl()
    Dim Cell As Range
    With Sheets("Tableur")

    For Each Cell In .Range("B7:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
    '--> check the value in T column in the corresponding row
        If Not IsEmpty(.Cells(Cell.Row, "T")) Then

            .Rows(Cell.Row).Copy Destination:=Sheets("genealogie").Rows(Cell.Row)

        End If
    Next Cell
    End With
End Sub

Excel: Copy a row to another worksheet if a certain cell is not empty

2 Answers