Run time error '1004' "Copy Method of Range Class Failed" when changing OS

Question

I have some code originally modifed from here. When there is a C in the first column of a row that row is deleted and saved in another sheet. It's for a todo list applicaiton.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    '   Code goes in the Worksheet specific module
    Dim rng As Range
        '   Set Target Range
        Set rng = Target.Parent.Range("A1:A200")
             '   Only look at single cell changes
            If Target.Count > 1 Then Exit Sub
            '   Only look at that range
            If Intersect(Target, rng) Is Nothing Then Exit Sub
            '   Action if Condition(s) are met
            Select Case Target.Text
               Case "C"
                   Target.EntireRow.Copy Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Offset(1)
                   Target.EntireRow.Delete



            End Select

End Sub

The code works wonderfully on Excel 2010 but fails with this error:

Run time error '1004' "Copy Method of Range Class Failed" 

Answer 1

Is this what you are trying? You need to switch off events else Target.EntireRow.Delete will refire the event. You may also want to see This

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lRow As Long

    '~~> For Excel 2007+
    'If Target.Cells.CountLarge > 1 Then Exit Sub        
    If Target.Cells.Count > 1 Then Exit Sub

    On Error GoTo Whoa

    Application.EnableEvents = False

    If Not Intersect(Target, Range("A1:A200")) Is Nothing Then
        If Target.Value = "C" Then
            With Sheets("Completed")
                '~~> Find next available row in the output sheet
                lRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1

                Target.EntireRow.Copy .Rows(lRow)
                Target.EntireRow.Delete
            End With
        End If
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Answer 2

Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Offset(1)

needs to be

Sheets("Completed").Cells(Sheets("Completed").Rows.Count, "A").End(xlUp).Offset(1)