VBA Paste Special Error Message after running macro twice

[Will85]

I am not sure why I am getting this error

PasteSpecial Method of Range Class Failed

My macro is activated while on sheet Control, it copies data from sheet Control and pastes it on sheet CY, then it goes back to sheet Control and clears out the old data.

If I immediately run the macro again, I get the error. If I change a single piece of the data I dont get the error. It wouldnt really make sense to immediately run the macro, but I am trying to manage what a user might do on accident.

I neve know when to use Application.CutCopyMode = False but adding it in here makes no difference.


Sub Test()
ActiveSheet.Unprotect
Range("E1:N1").Select
Selection.Copy

Sheets("CY").Select
ActiveSheet.Unprotect
Range("B" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveSheet.Protect

Sheets("Control").Select

Range("C1").Value = "N"

Range("C2,C5:C6,A10,C10,A14,C14,C16:C18").Select
Selection.ClearContents
ActiveSheet.Protect


End Sub

 

[MARK858]

Can you see if you get the same issue with the code below

Sub Test2()
   Sheets("Control").Unprotect
   Sheets("Control").Range("E1:N1").Copy

    With Sheets("CY")
        .Unprotect
        .Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        .Protect
    End With

    With Sheets("Control")

        .Range("C1").Value = "N"

        .Range("C2,C5:C6,A10,C10,A14,C14,C16:C18").ClearContents
        .Protect

    End With

End Sub