Programmatically pasting a Function locks macro

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

Post the code that is causing the problem.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"diglas1 via OfficeKB.com" <u22313@uwe> wrote in message
news:61e07164f8831@uwe...
>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

Chip, thanks for your response...and your excellent site !
I'll paste the macro code, though I guess what you need to see is the
spreadsheet...I don't think OfficeKB allows an attachment, but I'll email it
if you want to give me an email...(spreadsheet is only 1 page and 67KB.)
Macro as recorded below makes no reference to your SumByColor function.

Sub addNewWeek()
Cells.Find(What:="click here", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Rows("22:31").Select
Selection.Copy
Rows("32:32").Select
Selection.Insert Shift:=xlDown
Range("A32").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-4]C+1"
Range("A33").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("A33").Select
Selection.AutoFill Destination:=Range("A33:A38"), Type:=xlFillDefault
Range("A33:A38").Select
Range("A38").Select
Selection.Font.Bold = True
Range("C32:D38").Select
Selection.ClearContents
Range("H40").Select
Selection.ClearContents
ActiveWindow.SmallScroll ToRight:=3
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 52
ActiveWindow.SmallScroll Down:=-25
Range("P40").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 1
Range("A38").Select
End Sub




Chip Pearson wrote:
>Post the code that is causing the problem.
>
>>I am trying to copy a 7 row block of code programmatically and
>>insert it
>[quoted text clipped - 40 lines]
>> 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