Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Method Failure - cont'd
Hello
I have created a macro that processes monthly input files. On a 'template' tab within the macro file, the macro performs calculations on the input data and then graphs the processed data for a given site location. After the data is graphed, a copy is made from the template tab to a new tab for each site location that appears on the input. The macro does seem to be working with no issues. I run the macro 2 times successfully to process 2 months of input file data. On the third month of data, it issues me an error Runtime error '1004'. Method 'Copy of object '_Worksheet' failed. I look at the content of the macro file after the failure, and it is as if it stopped copying in mid copy because some of the data is copied to the new tab, and other data like the graph is not copied yet. I seem to be getting the same failure in the very same spot with the file combinations I chose. Here is where it becomse odd....When I run the macro for the first 2 months successfully, then save the macro and exit out of Excel completely. Then I re-open and process the 3rd month input file (i.e. the one that failed before), the macro runs to completion with no issues. I have verified that the input file is not corrupt in any way. Lastly if I continue to run the macro and re- process the same input file over and over again, it eventually fails about 2 runs later. The actual statement receiving the failure is this: ' Copy tab to new site tab wsChartsTab.Copy After:=ThisWorkbook.Sheets(5 + numofsites) I verified when the failure occurs numofsites is valid and the correct number. My question is, when you have creation of new tabs in this way, is there something I should clear each time like a buffer or something? I have tested a number of different input file possibilities, and it seems intermittent as to why it fails where it does (i.e. not based on input data). Or does someone have any ideas to suggest as to what else to test, or what it could be? ============ I have an update for the above. I have now updated the macro to completely clear the clipboard using the logic from this Pearson website. http://www.cpearson.com/EXCEL/Clipboard.aspx ClearClipboard section Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long Public Declare Function EmptyClipboard Lib "user32" () As Long Public Declare Function CloseClipboard Lib "user32" () As Long Sub ClearClipboard() OpenClipboard (0&) EmptyClipboard CloseClipboard End Sub This manages to get past where the failure occurs to near the end of the input file. But it still fails on the same copy line further along. Has anyone experienced this kind of issue with a Copy before and if yes, any suggestions? Thanks, Enzo Thanks in advance, Enzo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Method Failure - cont'd
Enzo -
Just a guess here, but if the data you are copy/pasting is large, you may need to do a Save after each paste. -- Daryl S "Enz" wrote: Hello I have created a macro that processes monthly input files. On a 'template' tab within the macro file, the macro performs calculations on the input data and then graphs the processed data for a given site location. After the data is graphed, a copy is made from the template tab to a new tab for each site location that appears on the input. The macro does seem to be working with no issues. I run the macro 2 times successfully to process 2 months of input file data. On the third month of data, it issues me an error Runtime error '1004'. Method 'Copy of object '_Worksheet' failed. I look at the content of the macro file after the failure, and it is as if it stopped copying in mid copy because some of the data is copied to the new tab, and other data like the graph is not copied yet. I seem to be getting the same failure in the very same spot with the file combinations I chose. Here is where it becomse odd....When I run the macro for the first 2 months successfully, then save the macro and exit out of Excel completely. Then I re-open and process the 3rd month input file (i.e. the one that failed before), the macro runs to completion with no issues. I have verified that the input file is not corrupt in any way. Lastly if I continue to run the macro and re- process the same input file over and over again, it eventually fails about 2 runs later. The actual statement receiving the failure is this: ' Copy tab to new site tab wsChartsTab.Copy After:=ThisWorkbook.Sheets(5 + numofsites) I verified when the failure occurs numofsites is valid and the correct number. My question is, when you have creation of new tabs in this way, is there something I should clear each time like a buffer or something? I have tested a number of different input file possibilities, and it seems intermittent as to why it fails where it does (i.e. not based on input data). Or does someone have any ideas to suggest as to what else to test, or what it could be? ============ I have an update for the above. I have now updated the macro to completely clear the clipboard using the logic from this Pearson website. http://www.cpearson.com/EXCEL/Clipboard.aspx ClearClipboard section Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long Public Declare Function EmptyClipboard Lib "user32" () As Long Public Declare Function CloseClipboard Lib "user32" () As Long Sub ClearClipboard() OpenClipboard (0&) EmptyClipboard CloseClipboard End Sub This manages to get past where the failure occurs to near the end of the input file. But it still fails on the same copy line further along. Has anyone experienced this kind of issue with a Copy before and if yes, any suggestions? Thanks, Enzo Thanks in advance, Enzo . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Method Failure - cont'd
Thanks Daryl for this suggestion. I tried your suggestion, but it
still fails unfortunately. I will continue to research. On Feb 12, 12:20*pm, Daryl S wrote: Enzo - Just a guess here, but if the data you are copy/pasting is large, you may need to do a Save after each paste. * -- Daryl S "Enz" wrote: Hello I have created a macro that processes monthly input files. *On a 'template' tab within the macro file, the macro performs calculations on the input data and then graphs the processed data for a given site location. *After the data is graphed, a copy is made from the template tab to a new tab for each site location that appears on the input. The macro does seem to be working with no issues. *I run the macro 2 times successfully to process 2 months of input file data. *On the third month of data, it issues me an error Runtime error '1004'. Method 'Copy of object '_Worksheet' failed. *I look at the content of the macro file after the failure, and it is as if it stopped copying in mid copy because some of the data is copied to the new tab, and other data like the graph is not copied yet. I seem to be getting the same failure in the very same spot with the file combinations I chose. *Here is where it becomse odd....When I run the macro for the first 2 months successfully, then save the macro and exit out of Excel completely. *Then I re-open and process the 3rd month input file (i.e. the one that failed before), the macro runs to completion with no issues. *I have verified that the input file is not corrupt in any way. *Lastly if I continue to run the macro and re- process the same input file over and over again, it eventually fails about 2 runs later. The actual statement receiving the failure is this: * * ' Copy tab to new site tab * * wsChartsTab.Copy After:=ThisWorkbook.Sheets(5 + numofsites) I verified *when the failure occurs numofsites is valid and the correct number. My question is, when you have creation of new tabs in this way, is there something I should clear each time like a buffer or something? I have tested a number of different input file possibilities, and it seems intermittent as to why it fails where it does (i.e. not based on input data). * Or does someone have any ideas to suggest as to what else to test, or what it could be? ============ I have an update for the above. I have now updated the macro to completely clear the clipboard using the logic from this Pearson website. http://www.cpearson.com/EXCEL/Clipbo...ClearClipboard section Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long Public Declare Function EmptyClipboard Lib "user32" () As Long Public Declare Function CloseClipboard Lib "user32" () As Long Sub ClearClipboard() * * OpenClipboard (0&) * * EmptyClipboard * * CloseClipboard End Sub This manages to get past where the failure occurs to near the end of the input file. *But it still fails on the same copy line further along. *Has anyone experienced this kind of issue with a Copy before and if yes, any suggestions? Thanks, * Enzo Thanks in advance, * Enzo .- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Method Failure | Excel Programming | |||
BorderAround method failure | Excel Programming | |||
OpenText Method failure | Excel Programming | |||
GoalSeek Method failure | Excel Programming | |||
Select method of Range class failure | Excel Programming |