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.

If you're making multiple copies of a worksheet in the same file, I'm
pretty sure excel will always fail after enough copies (4 to 8) have
been made. This has been my experience.

If you save, close and reopen the file after every 3 or 4 sheets, I
think you can avoid this. Perhaps someone else knows more about it.

Good luck

Brian

See
http://support.microsoft.com/kb/210684

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Brian Murphy" <(E-Mail Removed)> wrote in message
news:151abe35-0505-4aef-912b-(E-Mail Removed)...
> If you're making multiple copies of a worksheet in the same file, I'm
> pretty sure excel will always fail after enough copies (4 to 8) have
> been made. This has been my experience.
>
> If you save, close and reopen the file after every 3 or 4 sheets, I
> think you can avoid this. Perhaps someone else knows more about it.
>
> Good luck
>
> Brian

You can just clear Static sheet and then copy /paste values from Update
sheet, then you do not have to save and close your workbook:

Private Function CreateNewsheet() As Boolean
Dim wks As Worksheet
Dim shStatic As Worksheet
Dim shUpdate As Worksheet

CreateNewsheet = False
Set shUpdate = ThisWorkbook.Worksheets("Spot Rate Update")
Set shStatic = ThisWorkbook.Worksheets("Spot Rate Static")

shStatic.UsedRange.Clear

shUpdate.UsedRange.Copy
shStatic.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Debug.Print Time()
CreateNewsheet = True
End Function

Regards,
Per

"Ron de Bruin" <(E-Mail Removed)> skrev i meddelelsen
news:(E-Mail Removed)...
> See
> http://support.microsoft.com/kb/210684
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Brian Murphy" <(E-Mail Removed)> wrote in message
> news:151abe35-0505-4aef-912b-(E-Mail Removed)...
>> If you're making multiple copies of a worksheet in the same file, I'm
>> pretty sure excel will always fail after enough copies (4 to 8) have
>> been made. This has been my experience.
>>
>> If you save, close and reopen the file after every 3 or 4 sheets, I
>> think you can avoid this. Perhaps someone else knows more about it.
>>
>> Good luck
>>
>> Brian
>

Thanks Gents, not only an explanation but a solution.

So again many thanks.



"Paul W Smith" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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.
>
>