VBA Select Data in Table that meets criteria

Question

I am trying to select cells in a table if another cell in the same table meets a certain condition, then copy the selected cells into a separate worksheet. I thought using an if then statement might workout best but Im sure there might be simpler ways to do this. I have tried two different options. I personally believe the second option is better than the first. But neither work.

Option 1

Dim Cell As Range
Dim EML As Range
For Each Cell In Worksheets("Project Directory").Range("Directory[OAC]")
For Each EML In Worksheets("Project Directory").Range("Directory[EMAIL]")
If Cell = "Y" Then
Worksheets("Data").Range("A1") = Worksheets("Project Directory").EML
End If

Next Cell

Option 2

If Worksheets("Sheet1").ListObjects("Table1").ListColumns(8).DataBodyRange = "Y" Then
Worksheets("Sheet2").Range("A1") = Worksheets("Sheet1").ListObjects("Table1").ListColumns(5)
End If

Use a filter.... then copy visible cells. – BigBen Mar 27 '20 at 18:34 — BigBen, Mar 27 '20 at 18:34

score 1 · Answer 1 · answered Mar 30 '20 at 07:38

you seem to be missing bits in your code: in the first one the loop is not closed. In the second option you don't have a loop. In both cases, all results will be written and overwritten to Sheet2, cell A1. If I adapt your code for those omissions, these are two examples that should work (untested). Cheers Option 1

Dim Cell As Range
Dim EML As Range
r = 1
For Each Cell In Worksheets("Project Directory").Range("Directory[OAC]")
  For Each EML In Worksheets("Project Directory").Range("Directory[EMAIL]")
    If Cell = "Y" Then
      Worksheets("Data").Range("A" & r).Value = Worksheets("Project Directory").EML
      r = r + 1
    End If
  Next EML
Next Cell

Option 2

Dim Tbl As ListObject
Set Tbl = Worksheets("Sheet1").ListObjects("Table1")
r = 1
For Each DataRw In Tbl.ListRows
    If DataRw.ListColumns(8).DataBodyRange = "Y" Then
        Worksheets("Sheet2").Range("A1").Offset(r, 0) = Tbl.ListColumns(5).Value
        r = r + 1
    End If
Next

VBA Select Data in Table that meets criteria

1 Answers1