Copying rows with specific values

[nitar]

Hi,
In my data, I have a column for enrollment status; enrolled or not enrolled. I'm trying to copy only rows that have "enrolled" out of about 200 rows using Excel VBA. Please what do I do?

Thanks for your help!

 

[Weaver]

Hi,
In my data, I have a column for enrollment status; enrolled or not enrolled. I'm trying to copy only rows that have "enrolled" out of about 200 rows using Excel VBA. Please what do I do?

Thanks for your help!

If you use autofilter to filter the 'enrolled' items, you can select and copy the rows remaining visible and then paste them to another sheet.

 

[nitar]

If you use autofilter to filter the 'enrolled' items, you can select and copy the rows remaining visible and then paste them to another sheet.

Thanks Weaver,
I have tried recording the macro, I would just like something more advanced if there is .

 

[Weaver]

Most VB solutions for this task are based on the filtering method, so with a few tweaks, the recorded macro should be where you'll end up. You might also be able to use the advanced filter, but that needs a bit of setting up beforehand


What's the start/end columns of the data? what row is the header in? what column is the criteria in? What's the criteria header (in case we go with the advanced filter method) and finally where's the data going?

 

[nitar]

OK, thanks. See below:
Start:End Column - 2:8
Header Row - 3
Criteria Column - 8
Criteria Header - Enrollment Status
New location - The next sheet (i.e. Sheet2)

 

[Weaver]

Try this. Change sheet names where required. You should have the data tab selected when you run it, although you could change this by changing

with activesheet

to

with sheets("mydata")

Sub filterToNewSheet()
    Dim dataRange As Range, critRange As Range, dumpRange As Range
    With ActiveSheet
        Set dataRange = .Range("B3", .Cells(Rows.Count, "H").End(xlUp))
    End With
    With Sheets("sheet2")
        Set critRange = .Range("A1:A2")
        Set dumpRange = .Range("B1")
    End With
    critRange(1, 1) = "Enrollment Status"
    critRange(2, 1) = "Enrolled"
    dataRange.AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=critRange, _
        CopyToRange:=dumpRange, _
        unique:=False
    critRange.EntireColumn.Delete
End Sub

It also assumes sheet2 is as yet unpopulated.