Excel VBA - Method 'Range' of object'_Worksheet' failed

Question

I have 3 subs that are run when a certain worksheet is activated within a workbook. However, it fails and I receive the following error:

Run-time error '-2147417848 (80010108)': Method 'Range' of object'_Worksheet' failed

The code that errors is:

Sub MoveData()
    Dim r As Long, lr2 As Long, lr As Long
    lr2 = Worksheets("Status").UsedRange.Rows.Count
    lr3 = Worksheets("Completed").UsedRange.Rows.Count
        If lr3 = 1 Then lr3 = 0
        For r = lr2 To 2 Step -1
            If Worksheets("Status").Range("A" & r).Value <> "" And Worksheets("Status").Range("X" & r).Value = "Y" And Worksheets("Completed").Range("A2").Value = "" Then
                Worksheets("Status").Cells(r, 1).Copy Destination:=Worksheets("Completed").Range("A" & lr3)
                Worksheets("Status").Cells(r, 2).Copy Destination:=Worksheets("Completed").Range("B" & lr3)
                Worksheets("Status").Cells(r, 3).Copy Destination:=Worksheets("Completed").Range("C" & lr3)
                Worksheets("Status").Cells(r, 4).Copy Destination:=Worksheets("Completed").Range("D" & lr3)
                Worksheets("Status").Cells(r, 5).Copy Destination:=Worksheets("Completed").Range("E" & lr3)
                Worksheets("Status").Cells(r, 6).Copy Destination:=Worksheets("Completed").Range("F" & lr3)
                Worksheets("Status").Cells(r, 7).Copy Destination:=Worksheets("Completed").Range("G" & lr3)
                Worksheets("Status").Cells(r, 8).Copy Destination:=Worksheets("Completed").Range("H" & lr3)
                Worksheets("Status").Cells(r, 9).Copy Destination:=Worksheets("Completed").Range("I" & lr3)
                Worksheets("Status").Cells(r, 10).Copy Destination:=Worksheets("Completed").Range("J" & lr3)
                Worksheets("Status").Cells(r, 11).Copy Destination:=Worksheets("Completed").Range("K" & lr3)
                Worksheets("Status").Cells(r, 12).Copy Destination:=Worksheets("Completed").Range("L" & lr3)
                Worksheets("Status").Cells(r, 13).Copy Destination:=Worksheets("Completed").Range("M" & lr3)
                Worksheets("Status").Cells(r, 14).Copy Destination:=Worksheets("Completed").Range("N" & lr3)
                Worksheets("Status").Cells(r, 15).Copy Destination:=Worksheets("Completed").Range("O" & lr3)
                Worksheets("Status").Cells(r, 16).Copy Destination:=Worksheets("Completed").Range("P" & lr3)
                Worksheets("Status").Cells(r, 17).Copy Destination:=Worksheets("Completed").Range("Q" & lr3)
                Worksheets("Status").Cells(r, 18).Copy Destination:=Worksheets("Completed").Range("R" & lr3)
                Worksheets("Status").Rows(r).Delete Shift:=xlUp
                Else:
            End If
        Next r
        For r = lr2 To 2 Step -1
            If Worksheets("Status").Range("A" & r).Value <> "" And Worksheets("Status").Range("T" & r).Value = "Y" And Worksheets("Completed").Range("A2").Value <> "" Then
                Worksheets("Status").Cells(r, 1).Copy Destination:=Worksheets("Completed").Range("A" & lr3 + 1)
                Worksheets("Status").Cells(r, 2).Copy Destination:=Worksheets("Completed").Range("B" & lr3 + 1)
                Worksheets("Status").Cells(r, 3).Copy Destination:=Worksheets("Completed").Range("C" & lr3 + 1)
                Worksheets("Status").Cells(r, 4).Copy Destination:=Worksheets("Completed").Range("D" & lr3 + 1)
                Worksheets("Status").Cells(r, 5).Copy Destination:=Worksheets("Completed").Range("E" & lr3 + 1)
                Worksheets("Status").Cells(r, 6).Copy Destination:=Worksheets("Completed").Range("F" & lr3 + 1)
                Worksheets("Status").Cells(r, 7).Copy Destination:=Worksheets("Completed").Range("G" & lr3 + 1)
                Worksheets("Status").Cells(r, 8).Copy Destination:=Worksheets("Completed").Range("H" & lr3 + 1)
                Worksheets("Status").Cells(r, 9).Copy Destination:=Worksheets("Completed").Range("I" & lr3 + 1)
                Worksheets("Status").Cells(r, 10).Copy Destination:=Worksheets("Completed").Range("J" & lr3 + 1)
                Worksheets("Status").Cells(r, 11).Copy Destination:=Worksheets("Completed").Range("K" & lr3 + 1)
                Worksheets("Status").Cells(r, 12).Copy Destination:=Worksheets("Completed").Range("L" & lr3 + 1)
                Worksheets("Status").Cells(r, 13).Copy Destination:=Worksheets("Completed").Range("M" & lr3 + 1)
                Worksheets("Status").Cells(r, 14).Copy Destination:=Worksheets("Completed").Range("N" & lr3 + 1)
                Worksheets("Status").Cells(r, 15).Copy Destination:=Worksheets("Completed").Range("O" & lr3 + 1)
                Worksheets("Status").Cells(r, 16).Copy Destination:=Worksheets("Completed").Range("P" & lr3 + 1)
                Worksheets("Status").Cells(r, 17).Copy Destination:=Worksheets("Completed").Range("Q" & lr3 + 1)
                Worksheets("Status").Cells(r, 18).Copy Destination:=Worksheets("Completed").Range("R" & lr3 + 1)
                Worksheets("Status").Rows(r).Delete Shift:=xlUp
                lr3 = lr3 + 1
            Else:
            End If
        Next r
End Sub

The highlighted line from debug is:

If Worksheets("Status").Range("A" & r).Value <> "" And Worksheets("Status").Range("X" & r).Value = "Y" And Worksheets("Completed").Range("A2").Value = "" Then

Would you be able to provide an explanation of why the error is occurring and what I can do to fix it?

Answer 1

It seems that you are referring to column values ( A, B, C, D, etc.) when referencing cells (r, 1), (r, 2), (r, 3), (r, 4), etc. rather than the row values, which is what lr2 is initialized to represent. You might be to fix this potential referencing error by reformatting the:

Worksheets("Status").Cells(r, 1).Copy Destination:=Worksheets("Completed").Range("A" & lr3 + 1) Worksheets("Status").Cells(r, 2).Copy Destination:=Worksheets("Completed").Range("B" & lr3 + 1) Worksheets("Status").Cells(r, 3).Copy Destination:=Worksheets("Completed").Range("C" & lr3 + 1

To the following code:

Worksheets("Status").Cells(1, r).Copy Destination:=Worksheets("Completed").Range("A" & lr3 + 1) Worksheets("Status").Cells(2, r).Copy Destination:=Worksheets("Completed").Range("B" & lr3 + 1) Worksheets("Status").Cells(3, r).Copy Destination:=Worksheets("Completed").Range("C" & lr3 + 1

Additionally, the Shift:=xlUp found in the line:

Worksheets("Status").Rows(r).Delete Shift:=xlUp

might be unnecessary, since I think the row that is being deleted is at the bottom of the tabular data.

Answer 2

I fixed the error by removing Range from if:

Old vba

If Worksheets("Status").Range("A" & r).Value <> "" And Worksheets("Status").Range("X" & r).Value = "Y" And Worksheets("Completed").Range("A2").Value = "" Then

New vba

If Worksheets("Status").Cells(r, 1).Value <> "" And Worksheets("Status").Cells(r, 24).Value = "Y" And Worksheets("Completed").Cells(2, 1).Value = "" Then

Answer 3

How many rows are in Worksheets("Status")

From that error I would imagine it is either zero or one. Has to be 2 or more with your loop

For r = lr2 To 2 Step -1