Excel Macro - Copy Row into New Sheet Based on Cell Value

Question

I'm trying to copy every row in this table where column O contains the value "proceed" from a sheet named "Initials" into a sheet called "Finals". I'm not too familiar with VBA and Excel in general so I was going off of an Excel tutorial on YouTube. However I keep getting the error "Run-time error: '438': Object doesn't support this property or method". I've included the code I'm working with for reference. I've seen similar questions but nothing quite like what I need help with. Any help is appreciated. Thanks!

Sub CopyRowBasedOnCellValue()

    Dim xRg As Range
    Dim xCell As Range
    Dim A As Long
    Dim B As Long
    Dim C As Long

    A = Worksheets("Initials").UsedRange.Rows.Count
    B = Worksheets("Finals").UsedRange.Rows.Count

    If B = 1 Then
        If Application.WorkseetFunction.CountA(Worksheets("Finals").UsedRange) = 0 Then B = 0
    End If

    Set xRg = Worksheets("Initials").Range("O1:O" & A)

    On Error Resume Next

    Application.ScreenUpdating = False

    For C = 1 To xRg.Count

        If CStr(xRg(O).Value) = "Proceed" Then
            xRg(O).EntireRow.Copy Destination:=Worksheets("Finals").Range("A" & B + 1)
            B = B + 1
        End If

        Worksheets("Finals").UsedRange.RemoveDuplicates Columns:=1, Header:=x1Yes
        Worksheets("Finals").UsedRange.SpecialCells(x1CellTypeBlanks).Delete x1ShiftUp

    Next

    Application.ScreenUpdating = True
End Sub

`WorkseetFunction` >> `WorksheetFunction` also `Header:=x1Yes` >> `Header:=xlYes` and `x1ShiftUp` >> `xlShiftUp`, `x1CellTypeBlanks` >> `xlCellTypeBlanks` You can avoid many of these types of typos by always using `Option Explicit` at the top of every module. — Tim Williams, Aug 20 '21 at 05:55
Do you think instead of looping through rows and then checking and finally copying, you may be able to use [Autofilter](https://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s)? — Siddharth Rout, Aug 20 '21 at 06:15
This is not a set list of data. I am going through the list and checking who is denied and who we will proceed with. I want whoever we proceed with to go to another excel spreadsheet. I fixed the typos and unfortunately still have issues — PAbarca912, Aug 24 '21 at 18:17

Excel Macro - Copy Row into New Sheet Based on Cell Value

0 Answers0