PasteSpecial failed
 

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

PasteSpecial failed
 

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


--
Don Guillett
SalesAid Software
Granite Shoals, TX

"CR" wrote in message
...
It seems that it fails if I close the workbook and then reopen it without
exiting Excel.
Office 2000.
Any Ideas?

Thanks again
Coy Robbins

"CR" wrote in message
...
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