Prev: prevent print
Next: Checking the Value of a Check Box in a Macro
From: Enz on 11 Feb 2010 14:43
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?

Thanks in advance,
Enzo

 | 
Pages: 1
Prev: prevent print
Next: Checking the Value of a Check Box in a Macro