Copy Method Failure - cont'd

[Enz]

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

 

[Daryl S]

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.

 

[Enz]

Thanks Daryl for this suggestion. I tried your suggestion, but it
still fails unfortunately. I will continue to research.