Avoid Update Values Prompts

[Alan]

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

 

[Otto Moehrbach]

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

 

[Alan]

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

 

[Alan]

Do you know of another way to do this? Alan

 

[Alan]

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

 

[Pauline Hackney]

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.

 

[Pauline Hackney]

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/tutorial...-parameters--types--multiple-row-inserts.aspx