PasteSpecial failed

  • Thread starter Thread starter CR
  • Start date Start date
C

CR

The following code snip runs fine five or ten times in a row and then fails
time after time with a 1004 "PasteSpecial method of range class failed"
message.
Can someone please tell me why it would work and then fail at random?

Sub Copy()


Sheets("Sheet2").Select
Range("A3:C55").Select
Selection.Copy
Sheets("Sheet3").Select
ActiveSheet.Unprotect
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select

'Alway fails on next line
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

Sheets("Sheet3").Cells(65536, 1).End(xlUp).Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveSheet.Protect
Sheets("Sheet2").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
Range("D1").Select

End Sub

Thanks for any help
Coy Robbins
 
Coy,

You do not have to select the worksheet to unprotect it. And, you do not
have to select anything to copy.

Sub NoSelect()
Sheets("Sheet3").Unprotect
x = Sheets("Sheet3").Cells(65536, "A").End(xlUp).Row + 1
Sheets("Sheet2").Range("A3:C55").Copy
Sheets("sheet3").Range("a" & x).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Sheets("sheet3").Protect
End Sub
 
Back
Top