Using conditional statements to copy specific rows to a new sheet

[GabrielJames]

Hi Everyone,

I've found a lot of material on this topic but can't get it to work for my purposes.

I'm working to reformat some desired data on a new sheet with a macro based on specific values in the "E" column. I'm hoping to copy over all the rows that don't have the "Fail" in the "E" column. An example of my raw data file is attached.

The final format i'm looking for on my new sheet is with the wanted "E" values in the new column "B", the original "B" values in the new column "C", and the original "C"/"D" values in new columns "D"/E" respectively.

Here's my attempt but I can't figure out what I need to do to fix it:

Sub Macro_2()
Dim ClassType As Variant
Dim ClassArea As Areas
Dim ClassRange As Range, r As Range
Dim n As Long
Set ClassRange = Sheets("Macro Clean Data").Columns(5)

With Sheets("Macro Final Data")
    For Each ClassType In ClassRange
        If ClassType <> "Fail" Then
            n = n + 1
            .Cells(n, 2).Value = ClassType
            .Cells(n, 3).Value = r(, -3).Value
            .Cells(n, 4).Value = r(, -2).Value
            .Cells(n, 5).Value = r(, -1).Value
        Next
    Next
    .Columns.AutoFit
    End With
End Sub

Help would be greatly appreciated, Thank you!!

[xladept]

Try this:

Sub GabrielJames(): Dim U As Range
Set U = ActiveSheet.UsedRange
U.AutoFilter Field:=5, Criteria1:="=" & "Fail"
U.SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
Columns(5).Cut
Columns(2).Insert
End Sub

[xladept]

You're welcome and thanks for the rep!