Copy marked lines to a new tab

[louacct3]

I want to add a new sheet (Sheet B) to my workbook that copies over all lines on an existing sheet (Sheet A) that are marked with an X.

So for example, I have the following data on Sheet A:

Item	Responsibility	Cost	Is Eligible for Credit?
Paint walls	Independent contractor	$3750
Fix toilets	Construction company	$500	X
Survey foundation	Independent contractor	$750
Rewire interior	Do it myself	$55	X

What I want to happen is for all of the lines that are marked "Is Eligible for Credit?" to populate themselves on Sheet B like this:

Item	Responsibility	Cost
Fix toilets	Construction company	$500
Rewire interior	Do it myself	$55

Adding or removing the 'X' on Sheet A should cause Sheet B to update.

Is there a function that can grab all of these lines, or does this need to be done with a VBA script/macro? Any advice on accomplishing this feat is appreciated.

 

[louacct3]

I know I can do this with formulas that look for the presence of the data in that last column, and only copy the data if the X exists...but how to do it gracefully so that it doesn't leave blank lines in between?

 

[Logit]

Option Explicit

Sub CopyYes()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
    
    
    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Sheet1")
    Set Target = ActiveWorkbook.Worksheets("Sheet2")
    Target.Range("A2:D1000").Delete
        
    j = 2     ' Start copying to row 1 in target sheet.
    
    
    For Each c In Source.Range("D2:D1000")   ' Do 1000 rows
        
        If c = "X" Or c = "x" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
        
    Next c
End Sub