Thanks Gents, not only an explanation but a solution.

So again many thanks.



"Paul W Smith" wrote in message
...
My Code:

Sub SaveMeFromThisShell()
If CreateNewsheet Then
ThisWorkbook.Save
DTime = Time
Application.OnTime DTime + TimeValue("00:01:00"), "SaveMeFromThisShell"
Else
MsgBox "There is a problem, the next OnTime event has not been set."
End If
End Sub


Private Function CreateNewsheet() As Boolean
Dim wks As Worksheet
'On Error GoTo ErrorEnd
CreateNewsheet = False
Application.DisplayAlerts = False
ThisWorkbook.Worksheets("Spot Rate Static").Delete
Application.DisplayAlerts = True

ThisWorkbook.Worksheets("Spot Rate Update").Copy
After:=ThisWorkbook.Worksheets(2)

Set wks = ThisWorkbook.Worksheets("Spot Rate Update (2)")
wks.Name = "Spot Rate Static"
wks.Cells.Copy
wks.Cells.PasteSpecial xlValues
Set wks = Nothing
Worksheets("RICcodes").Cells.Replace What:="=#REF",
Replacement:="='Spot Rate Static'"
ThisWorkbook.Worksheets("RICcodes").Select
Debug.Print Time()
CreateNewsheet = True
End Function


The above code is designed to, copy a worksheet and then copy pastespecial
the values this fixing them. It is designed to do this every minute.

I am testing this code and all appears to work fine. However after a
random period of time a "Run-time error '1004': copy method of worksheets
class failed" appears at the "ThisWorkbook.Worksheets("Spot Rate
Update").Copy After:=ThisWorkbook.Worksheets(2)" line. I know the code
executs successfully because the intermediate window is filled with times,
one minute apart, showing execution.

The PC is a stand alone one, and has nothing else running on it. I have
tested the code with another Excel workbook open and being worked upon and
this does not seem to cause the code any issues, it executes in the
background successfully.

I cannot understand what is changing to cause the sheet not to be able to
be copied. I stop the code after the debug, and immediately run the
SaveMeFromThisShell procdure to re-start the process, then after a random
period sofar always more than a hour and sometimes over three hours, the
error re-occures.

Can anyone explain this, and how I change the code to make it work without
the error occuring. As the code shows I did have an 'On Error Resume
Next' line in but would prefer to understand the error if possible before
resorting to this.