Loop through Columns, and if they contain "YES, do a function, otherwise next Column

[roshea92]

Hi Everyone,

I am looking for VBA code which will loop through columns J:W.
If the given column contains the text "YES", then I would like to filter that column and copy the data into another sheet called "Numbers"

Otherwise I would like to loop onto the next column and do the same action, copying the data from the next column two lines underneath the previous copied data (simple offset function).

Any help is greatly appreciated.

 

[NoSparks]

Assuming your originating sheet has at least 1 header row, perhaps you can adapt something like this

Sub Testing()
    Dim col As Integer
    Dim lr As Long      'last row
    Dim wr As Long      'write row
    Dim ws As Worksheet
    Dim dest As Worksheet
    
Set ws = Sheets("Sheet1")   '<~~ change to suit
Set dest = Sheets("Sheet2") '<~~ change to suit

Application.ScreenUpdating = False

For col = 10 To 23      ' J thru W
    If Application.WorksheetFunction.CountIf(ws.Columns(col), "YES") > 0 Then
        lr = ws.Cells(Rows.Count, col).End(xlUp).Row
        wr = dest.Cells(Rows.Count, 1).End(xlUp).Row + 2
        'filter
        ws.Columns(col).AutoFilter Field:=1, Criteria1:="YES"
        'copy   'offset is number of rows omitted from top
        ws.UsedRange.Offset(1).Copy dest.Range("A" & wr)
        'remove filter
        ws.Columns(col).AutoFilter
    End If
Next col

Application.ScreenUpdating = True

End Sub