Append row with condition

November 18, 2021

I’m trying to create a VBA sub to append rows from a "child" workbook into an excel "masterfile", only if the rows contain "nonconformity" in the 4th cells (the idea is presented here: picture)
The workbook child is wkUP and the master is wkDW, and so far my loop isn’t working, it’s appending wrong rows…

I guess my issue is in the i and the length of the table ?

Dim i, iLastRow As Integer

Dim oLastRow As ListRow
Dim srcRow As Range
iLastRow = wkUP.Sheets("DATA").ListObjects("TAB1").ListRows.Count
For i = 1 To iLastRow
    If wkUP.Sheets("DATA").Cells(i, 4).Value = "nonconformity" Then
        Set srcRow = wkUP.Sheets("DATA").ListObjects("TAB1").ListRows(i).Range
        Set oLastRow = wkDW.Worksheets("NC").ListObjects("tbl_NC").ListRows.Add
    srcRow.Copy
    oLastRow.Range.Cells(9).PasteSpecial Paste:=xlPasteValues
    End If
Next

End If

If someone see where the code is wrong, I would really appreciate, I’m kinda lost.. :'(

>Solution :

You’re using i in two different contexts:

In relation to the sheet: .Cells(i, 4)
In relation to the table: .ListRows(i)

You need to be consistent; here is one approach that uses i only in reference to the table.

Dim tbl As ListObject
Set tbl = wkUP.Sheets("DATA").ListObjects("TAB1")

Dim lastRow As Long, i As Long
lastRow = tbl.ListRows.Count

For i = 1 To lastRow
    If tbl.ListColumns("Answer").DataBodyRange.Cells(i).Value = "nonconformity" Then
        Set srcRow = tbl.ListRows(i).Range
        ...
    End If
Next