Avoiding the 'Update Values' dialogue
 

My Code shown below (simply copying the formulas from the line above to a
number of newly inserted rows):-

The problem is that my users ocasionally add links to other workbooks
somewhere in the row which I am copying (for good reasons of their own). The
xlPasteformulas line then causes the 'Update Values' dialogue to pop up,
which needs to be cancelled before the macro will proceed. This routine runs
unattended at night, so this is not good ! Is there a way I can switch the
update values to default to cancel or no on the copy ? I have tried setting
application.displayalerts to False in the line above the paste, but that
doesn't help.


Rows(Insertpoint - 1).Select
Selection.Copy
Rows(Insertpoint & ":" & (Insertpoint + NumberOfRows)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False, update=False

Avoiding the 'Update Values' dialogue
 

Application.DisplayAlerts = False

'your macro

at the end of the macro

Application.DisplayAlerts = True


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=539622

Avoiding the 'Update Values' dialogue
 

I don't think I have ever run into that problem. What version of excel are
you using?

What do you want to happen to cells that raise that message - do you want
them to update or do you want to not copy the formula to avoid the problem.
Did you previously open the workbook with UpdateLinks set to 0 (suppress the
udating of links).

--
Regards,
Tom Ogilvy


"Quiller38" wrote:

My Code shown below (simply copying the formulas from the line above to a
number of newly inserted rows):-

The problem is that my users ocasionally add links to other workbooks
somewhere in the row which I am copying (for good reasons of their own). The
xlPasteformulas line then causes the 'Update Values' dialogue to pop up,
which needs to be cancelled before the macro will proceed. This routine runs
unattended at night, so this is not good ! Is there a way I can switch the
update values to default to cancel or no on the copy ? I have tried setting
application.displayalerts to False in the line above the paste, but that
doesn't help.


Rows(Insertpoint - 1).Select
Selection.Copy
Rows(Insertpoint & ":" & (Insertpoint + NumberOfRows)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False, update=False

Avoiding the 'Update Values' dialogue
 

It is Excel 2003. As you guessed I open the workbook with UpdateLinks:= 0 to
avoid the update links dialogue popping up on the open. What I want is to
paste formulas without Updating Links.

"Tom Ogilvy" wrote:

I don't think I have ever run into that problem. What version of excel are
you using?

What do you want to happen to cells that raise that message - do you want
them to update or do you want to not copy the formula to avoid the problem.
Did you previously open the workbook with UpdateLinks set to 0 (suppress the
udating of links).

--
Regards,
Tom Ogilvy


"Quiller38" wrote:

My Code shown below (simply copying the formulas from the line above to a
number of newly inserted rows):-

The problem is that my users ocasionally add links to other workbooks
somewhere in the row which I am copying (for good reasons of their own). The
xlPasteformulas line then causes the 'Update Values' dialogue to pop up,
which needs to be cancelled before the macro will proceed. This routine runs
unattended at night, so this is not good ! Is there a way I can switch the
update values to default to cancel or no on the copy ? I have tried setting
application.displayalerts to False in the line above the paste, but that
doesn't help.


Rows(Insertpoint - 1).Select
Selection.Copy
Rows(Insertpoint & ":" & (Insertpoint + NumberOfRows)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False, update=False