Hello,

You are doing Selection.Copy but then pasting waay down below, it's
possible the clipboard is being emptied in the middle of your macro
since you have so many statements in between them.


HTH,
JP


On Oct 26, 4:25 pm, Jon wrote:
I have the following code and am trying to copy and paste special values to
another sheet in another workbook (in the next empty row). There are 45
columns (1 row) of data being pasted. It runs successfully half the time and
with the remainder I get an error as though my clipboard is empty
(PasteSpecial method of Range class failed). Does anyone know what could be
wrong with my code?
Thanks!

Sub savedata()
CoCo = 1222
Stamp = Format(Now, "mm/dd/yyyy Hh:mm:ss")
Range("Q1").Select
Selection.CurrentRegion.Select
Selection.Copy
Workbooks.Open Filename:= _
"S:\USS Financial Services\Accounting Services\Completed Recons\Cash
Management\" & CoCo & "-Recons Current Month.xls"
ActiveSheet.Activate
ActiveSheet.Unprotect Password:="xxxxxxx"
Range("B3").Select
With Selection.CurrentRegion
.Cells(.Cells.Count).Activate
End With
ActiveCell.Offset(Count + 1, -45).Select
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(Count + 0, 45).Select
ActiveCell.Value = Stamp
ActiveCell.Offset(Count + 1, -45).Select
ActiveSheet.Protect Password:="xxxxxxx"
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub