I am trying to copy a 7 row block of code programmatically and insert it
immediately below, then perform some simple edits.
It is a simple block with column 1&2 showing days of the week and date and in
a further column a single cell showing the week number(from April, tax year).
I also have a cell using a function copied from Chip Pearson's website,
called SumByColor, which sums, as it says, numbers above of particular
colours.

I generate the code via a recorded macro, copying the existing block, then
inserting the cells below, followed by simple edits. This works manually but
halts without error following the "Selection.Insert" command generated in the
macro. I see by stepping into the code that it somehow calls the SumByColor
function and halts in this function, giving the Excel "ding" tone, but no
error.
The area originally copied is now surrounded by moving dotted lines, I
suppose as this was the last selection, and all the cells above using the
SumByColor function have lost their values(now containing #VALUE!...as an
aside, F2 Enter seems to refresh these individually).

I searched for solutions and tried, for example,
setting "Application.Volatile True" in the function, and commenting it out
setting Tools, Options, Calculations, Automatic on/off
setting Tools, Options, Calculations, Save external link values on/off

I suspect that it is caused by the function wanting to recalculate all cells
that use it...though they do not get calculated !
Any ideas on what is happening and ways around, or defeat it ?
Thanks in anticipation

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200606/1