How to conditionally add rows to a spreadsheet based on cell values in Excel
<p>I am looking to do the following using Excel:</p>

<p>Below is a table of booleans and unique identifiers. </p>

<p>This is what I would like to achieve. If the first column says "No", I would like the code to automatically add a whole new row in a separate spreadsheet with the unique identifier in the first column of the new row (shown below is spreadsheet 1).</p>

<p><a href="https://i.stack.imgur.com/0fA2M.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0fA2M.png" alt="Spreadsheet 1"></a></p>

<p>In this case C4 and C5 would be the two rows exemplified below (spreadsheet 2).</p>

<p><a href="https://i.stack.imgur.com/vvuha.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vvuha.png" alt="Result"></a></p>

<p>Code updated:</p>

<pre><code>Sub AddID()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet

    Set Source = ActiveWorkbook.Worksheets("Questionnaire")
    Set Target = ActiveWorkbook.Worksheets("AI Tracker")

    j = 1
    For Each c In Source.Range("C4:C54")
        If c = "No" Then
           Target.Cells(j + 4, "A").Value = c.Offset(, 1).Value
           j = j + 1
        End If
    Next c
End Sub
</code></pre>

<p>This updates the target worksheet correctly but I need it to do update if the Source worksheet is altered (i.e. if something is changed to No, the function should add the new row to the Target sheet).</p>

<p>I have made the following code to detect changes but it does not work:</p>

<pre><code>Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, ActiveWorkbook.Worksheets("Questionnaire").Range("C4:C54")) _
       Is Nothing Then
        Call Module2.AddID
        MsgBox "Cell has changed"
    End If
End Sub
</code></pre>