Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Anyone Explain This - Worksheets Copying Issue
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Anyone Explain This - Worksheets Copying Issue
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Anyone Explain This - Worksheets Copying Issue
See
http://support.microsoft.com/kb/210684 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brian Murphy" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Anyone Explain This - Worksheets Copying Issue
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" skrev i meddelelsen ... See http://support.microsoft.com/kb/210684 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brian Murphy" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Anyone Explain This - Worksheets Copying Issue
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Formula Issue | Excel Discussion (Misc queries) | |||
issue copying data | Excel Discussion (Misc queries) | |||
Chart Copying Issue | Charts and Charting in Excel | |||
Issue with copying Text. | Excel Discussion (Misc queries) | |||
Help (please, please) copying and pasting issue | Excel Programming |