Can Anyone Explain This - Worksheets Copying Issue

[Paul W Smith]

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.

 

[Brian Murphy]

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

 

[Ron de Bruin]

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

 

[Per Jessen]

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

 

[Paul W Smith]

Thanks Gents, not only an explanation but a solution.

So again many thanks.