Cutting data to another sheet in VBA

[Shmevan11]

I am new to VBA so I am hoping you all can help. I have a data set that I want to cut and paste into 2 different sheets. I want to cut the rows that have data in column F to "Pending" and paste beneath the last row. The rows without data in column F would go to "Failures" and paste beneath the last row. There is always data in column A but the number of rows varies daily so it's not in a set range. Any advice?

 

[John_w]

Try this macro, changing the "Data" in the code to the name of the data sheet.

Public Sub Move_Rows()

    Dim r As Long, lrPending As Long, lrFailures As Long
    
    With Worksheets("Pending")
        lrPending = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
    With Worksheets("Failures")
        lrFailures = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
    
    With Worksheets("Data")
        For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            If Not IsEmpty(.Cells(r, "F").Value) Then
                .Rows(r).Copy Destination:=Worksheets("Pending").Rows(lrPending)
                lrPending = lrPending + 1
            Else
                .Rows(r).Copy Destination:=Worksheets("Failures").Rows(lrFailures)
                lrFailures = lrFailures + 1
            End If
        Next
        .Rows("2:" & r - 1).Delete
    End With
    
End Sub

 

[Shmevan11]

This worked! Thank you.

If you don't mind explaining, which lines would need to be changed to change the column searched? Or how would I change it to cut a cell or column instead? Is it possible to paste and additional row below the last row(so there would be a blank line between the old data and new)?

Thanks again!

 

[John_w]

The "F" in If Not IsEmpty(.Cells(r, "F").Value) Then is the column being searched.

Cutting a cell or column - best to record a macro and see if you can incorporate the code into the macro.

Blank row - not sure what you mean by old and new data. If you mean between runs of the Move_Rows macro then maybe just increase the starting row on the destination sheets:

    With Worksheets("Pending")
        lrPending = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
    With Worksheets("Failures")
        lrFailures = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
    lrPending = lrPending + 1
    lrFailures = lrFailures + 1

Just a guess because I don't know what you mean.