Repetitive Update Values Dialog box - Excel

Question

On trying to copy a lot of lines from one excel to another, a repetitive "Update Values" dialog box keeps popping up. Even on choosing the file name, it keeps popping up and there are more than 500 lines to be pasted. any solutions please? thanks in advance.

Answer 1

Hello there,

Thanks for coming to the community.

The “Update Values” error in Excel usually occurs when you’re trying to copy cells that contain a formula referencing another Excel file.

Here are a few solutions you can try. I have only used the first option and it helped but you can try other options if the first one doesn't work for you.

1.  Change the name of the referenced worksheet: If the name of the referenced worksheet starts with a number, try changing it. For example, change 2011Files to Files2011. Then try copying and pasting again.
   

2.  Use a macro to toggle the display of the alert on and off: You can write a simple macro to toggle the display of the “Update Values” alert on and off. Here’s an example of such a macro:
   

Sub terfuge()

If Application.DisplayAlerts Then Application.DisplayAlerts = False

Else

Application.DisplayAlerts = True

End If

End Sub

After inserting this macro, you can assign a keystroke combination to it. Pressing the keystroke combination will toggle the display of the “Update Values” alert on and off.

3.  Check the cell reference: Make sure the cell reference is correct when copying the cell before pasting. If necessary, convert it into a Mixed or Absolute reference as per your need.
   

4.  Select the upper-left cell instead of the whole range before you paste: Click the cell where you want the upper-left cell of the copied data to appear. On the Home tab, click Paste
   

I pulled the above steps from answers.microsoft.com

I hope any of the above ideas help.

Regards, Rita.