On Sep 9, 10:31*pm, "Otto Moehrbach"
wrote:
Alan
* * Do you mean that when you copy a formula from one workbook to another
that a link is made to the old workbook? *If so, the cure is to not copy
formulas. The idea is to change the formulas so that they are no longer
formulas, then copy them, then change them back to formulas at the
destination. *

Not exactly, but your approach may still work.

When I copy the formulas, the formulas appear correct in the
destination workbook (not referencing the source workbook). The
formulas reference worksheets that did not exist in the source
workbook but do exist in the destination workbook. So, once I copy
them, it asks to update their values.

Alan

Do you know of another way to do this? Alan

Otto,
Your idea did work. Finally got rid of the prompts. Alan

I know this thread is old, but I need to revisit this!

I am building an Excel document that pulls data from fields in other documents via links. These other documents are created for me each day by my employees, so in the master document I'm creating there is a tab (workbook) for each employee and many cells on each tab that link to the corresponding document for that day for that person. With 12 employees there are many source documents, and therefore, many many links.

I want to create the master document for the year in one sitting and get it done, but some of those source documents (any beyond today) have not been created yet. So when I try to copy and paste a tab in the sheet to make a new tab for another employee, I get hundreds of "Update Values" error pop-ups that I have to hit the Cancel button for each time or the red "window close" button in the upper right corner of the pop-up. Either way, my hand is starting to hurt and I'm getting super frustrated.

The reason I'm attaching this question to this thread is that I tried the above solution, but as soon as I replace the "$$$$$" with the "=" again, I get the same volume of "Update Values" pop-up boxes. Either way, it's a link searching for a file that does not exist (on purpose).

Isn't there any way to disable Excel's desire to "Update Values" until I'm finished finding/replacing everything? After all, when I open the document, it updates all the links in one fell swoop which would be MUCH easier and much preferred than this way!

Thanks in advance.

On Wednesday, September 09, 2009 9:47 PM Alan wrote:

After I copy cells (including formulas) from one workbook to another
using VBA (InWS.Cells.Copy OutWS.Cells), I get multiple prompts to
"Update Values".

Is there any way to avoid this?

The original workbook contains formulas, many of which are not valid
in its context, but they are used in the destination workbook.

Thanks, Alan


On Wednesday, September 09, 2009 10:31 PM Otto Moehrbach wrote:

Alan
Do you mean that when you copy a formula from one workbook to another
that a link is made to the old workbook? If so, the cure is to not copy
formulas. The idea is to change the formulas so that they are no longer
formulas, then copy them, then change them back to formulas at the
destination. For instance, in the source workbook, select the range that
incorporates all the formulas you want to copy. Then do Edit - Replace, and
replace all the equal signs with $$$$$. That changes all the formulas to
simple text. Copy/paste. Then, in the destination workbook, do the
opposite with Edit - Replace. Done. Otto


On Saturday, October 24, 2009 11:57 AM Alan wrote:

wrote:
ther
y

Not exactly, but your approach may still work.

When I copy the formulas, the formulas appear correct in the
destination workbook (not referencing the source workbook). The
formulas reference worksheets that did not exist in the source
workbook but do exist in the destination workbook. So, once I copy
them, it asks to update their values.

Alan


On Saturday, October 24, 2009 11:58 AM Alan wrote:

Do you know of another way to do this? Alan


On Saturday, October 24, 2009 11:58 AM Alan wrote:

Otto,
Your idea did work. Finally got rid of the prompts. Alan


Submitted via EggHeadCafe
SharePoint 2010 Using External List
http://www.eggheadcafe.com/tutorials...rnal-list.aspx

P.S. I wish there was a "Cancel All" button!

- Pauline

Submitted via EggHeadCafe
SQL Server Table Valued Parameters / Types - Multiple Row Inserts
http://www.eggheadcafe.com/tutorials...w-inserts.aspx

Hi Pauline,

I know I probably do not write to you but to anyone who reads this thread some time in the future...

I spend some hours not finding a solution until it dawned on me. For me Excel prompted me to update values because there was a typo in the link path I was trying to link to, without me noticing this.

Hope this can help somebody out there.