Moving rows to a different worksheet based on multiple criteria

Hi all,

I am using the following code to move rows from my worksheet if the cell in column K = Complete.

I need to also move rows if the cell in column K = Cancelled. The following is the code I am using. I'm not great with VBA yet so i am having a problem adding the 2nd criteria of cancelled. Can anyone tell me how to amend my code to include Cancelled as a critreria?

Sub CopyRequests()

Worksheets("SOR Requests").Unprotect Password:="DC2"
Worksheets("Completed SOR's").Unprotect Password:="DC2"

    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim J As Long
    Dim lngR As Long
    
    I = Worksheets("SOR Requests").UsedRange.Rows.Count
    J = Worksheets("Completed SOR's").UsedRange.Rows.Count
    
    If J = 1 Then
        If Application.WorksheetFunction.CountA(Worksheets("Completed SOR's").UsedRange) = 0 Then J = 0
    End If
    
    Application.ScreenUpdating = False
    
    For lngR = I To 10 Step -1
        If Application.CountIf(Worksheets("SOR Requests").Rows(lngR), "Complete") > 0 Then
                With Worksheets("SOR Requests").Rows(lngR)
                Worksheets("Completed SOR's").Rows(J + 1).Value = .Value
                .Delete
            End With
            J = J + 1
        End If
    Next lngR
    
     
    Application.ScreenUpdating = True
    
    Range("B10:B200").Formula = "=IF(A10="""","""",WORKDAY(A10,9,Holidays!$A$1:$A$8))"
    Range("C10:C200").Formula = "=IF(A10="""","""",NETWORKDAYS($A$1,B10,Holidays!$A$1:$A$8))"
    
Worksheets("SOR Requests").Protect Password:="DC2"
Worksheets("Completed SOR's").Protect Password:="DC2"

End Sub

Hi Tfagan1978

Are you sure this is the correct code posted. Why not upload a sample workbook.

Hi tfagan,

This looks like an Advanced Filter problem to me, more than a VBA problem. Learn a little about Advanced Filters using a filter of Complete OR Canceled.

http://www.excel-easy.com/examples/advanced-filter.html

Try this for starters.

Set ws1 = Sheets("SOR requests")
Set ws2 = Sheets("Completed SOR's")
I = Application.CountA(ws1.Columns(1)) + 1
J = Application.CountA(ws2.Columns(1)) + 5
For lngR = I To 10 Step -1
    If ws1.Cells(lngR, 11) = "Completed" Or ws1.Cells(lngR, 11) = "Cancelled" Then
        ws2.Cells(J + 1, 1).Resize(, 16).Value = ws1.Cells(lngR, 1).Resize(, 16).Value
        ws1.Rows(lngR).Delete
    End If
Next