Vba macro to copy row from table if value in table meets condition

Question

i'm trying to make a macro which:

1. goes through a table
2. looks if value in column B of that table has a certain value
3. if it has, copy that row to a range in an other worksheet

The result is similar to filtering the table but I want to avoid hiding any rows

I'm kinda new to vba and don't really know where to start with this, any help much appreciated.

Answer 1

Try it like this:

Sub testIt()
Dim r As Long, endRow as Long, pasteRowIndex As Long

endRow = 10 ' of course it's best to retrieve the last used row number via a function
pasteRowIndex = 1

For r = 1 To endRow 'Loop through sheet1 and search for your criteria

    If Cells(r, Columns("B").Column).Value = "YourCriteria" Then 'Found

            'Copy the current row
            Rows(r).Select 
            Selection.Copy

            'Switch to the sheet where you want to paste it & paste
            Sheets("Sheet2").Select
            Rows(pasteRowIndex).Select
            ActiveSheet.Paste

            'Next time you find a match, it will be pasted in a new row
            pasteRowIndex = pasteRowIndex + 1


           'Switch back to your table & continue to search for your criteria
            Sheets("Sheet1").Select  
    End If
Next r
End Sub

Answer 2

That is exactly what you do with an advanced filter. If it's a one shot, you don't even need a macro, it is available in the Data menu.

Sheets("Sheet1").Range("A1:D17").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Sheet1").Range("G1:G2"), CopyToRange:=Range("A1:D1") _
    , Unique:=False