Excel : la boîte « Update Values » qui se répète au collage — causes, solutions et VBA

Vous copiez des centaines de lignes d’un classeur Excel vers un autre et la boîte « Update Values » surgit en boucle ? Voici comment stopper immédiatement l’alerte, comprendre la cause exacte et choisir la meilleure méthode (avec ou sans VBA) pour coller sereinement, même avec >500 lignes.

Sommaire

Vue d’ensemble du problème

Lors d’un collage massif entre classeurs, Excel tente de résoudre les références. S’il détecte des liens externes (formules, noms, graphiques, validations, MFC, etc.) vers des fichiers introuvables ou fermés, il affiche la boîte « Update Values » pour que vous choisissiez la source. Avec un bloc conséquent, l’opération est répétée pour chaque lien distinct, d’où la sensation de boucle sans fin.

Pourquoi la boîte « Update Values » s’affiche en boucle ?

La cause la plus fréquente est la présence de liaisons externes dans ce que vous collez : références du type [Source.xlsx]Feuil1!A1, noms définis renvoyant vers d’autres fichiers, séries de graphiques liés, règles de MFC ou listes de validation appuyées sur un nom externe, etc. À chaque cellule, objet ou règle contenant un lien, Excel réessaie.

Symptôme	Cause probable	Remède express
« Update Values: Source.xlsx » s’ouvre à répétition pendant le collage	Formules avec références externes vers un ou plusieurs classeurs	Coller en Valeurs uniquement ou ouvrir le(s) classeur(s) source avant de coller
L’alerte revient même après avoir choisi un fichier	Plusieurs sources différentes, noms définis externes ou objets (graphique, segment) inclus	Nettoyer les liens (Gestionnaire de noms, graphiques, MFC, validations) ou Rompre les liaisons
Le bouton Modifier les liaisons n’apparaît pas	Excel n’expose le bouton que si des liens sont détectés dans le classeur actif	Rechercher « [ » dans les formules, inspecter noms/objets, ou utiliser une macro de listing
Lenteur extrême au collage	Calcul automatique + liens externes sur très grand volume	Passer temporairement le calcul en Manuel, coller, puis recalculer

Solutions immédiates si les formules ne sont pas requises

Coller en Valeurs uniquement : Accueil → Coller → Collage spécial → Valeurs.
Cette option supprime toute formule et empêche la création de nouvelles liaisons.
Coller depuis la cellule en haut à gauche sans pré‑sélectionner toute la zone cible, puis étendre si besoin. Excel gère mieux l’alignement et limite les invites liées aux objets/formats annexes.
Éviter le collage d’objets : si votre sélection comprend des graphiques, segments, images ou formes, utilisez Collage spécial → Valeurs (pas « Conserver le formatage »), ou copiez uniquement les cellules (sans objets).
Accélérer (optionnel) : Formules → Options de calcul → Manuel pendant l’opération, puis F9 pour recalculer à la fin.

Conserver les formules tout en maîtrisant les liaisons

Si vous tenez aux formules, trois approches fiables existent.

Ouvrir les classeurs sources avant de coller

Ouvrez le(s) classeur(s) référencé(s) (Source.xlsx, etc.).
Collez vos cellules dans le classeur cible.
Dans le classeur cible, Données → Modifier les liaisons (s’il apparaît) → Rompre les liaisons (convertit en valeurs) ou Modifier la source pour repointer vers le bon fichier ou vers lui‑même si la structure est identique.

Astuce : Si la destination a la même structure que la source, vous pouvez repointer vers le classeur actuel pour transformer les références externes en références locales.

Nettoyer les liaisons externes après collage

Rechercher « [ » dans Tout le classeur en cherchant dans les Formules : les références externes contiennent le nom du fichier entre crochets, ex. [Budget.xlsx]Feuil1!A1.
Gestionnaire de noms (Formules → Gestionnaire de noms) : supprimez ou éditez les noms qui pointent vers [AutreFichier.xlsx] ou vers un chemin 'C:\Dossier\[Fichier.xlsx]Feuil1'.
Mises en forme conditionnelles : Accueil → Mise en forme conditionnelle → Gérer les règles → vérifier les formules contenant des liens externes.
Validations de données : Données → Validation des données → onglet Paramètres → Liste alimentée par =AutreClasseur.xlsx!Nom → remplacez par une plage locale.
Graphiques : sélectionnez chaque série → regardez la barre de formule : si vous voyez ='[Source.xlsx]Feuil1'!$B$2:$B$100, remplacez par une plage du classeur cible.

Transformer les références externes en références locales par remplacement

Si les feuilles et plages existent dans la destination, un Rechercher/Remplacer peut convertir les liens :

Dupliquez le classeur cible pour sécurité.
Ctrl+H, recherchez \[Source.xlsx\] (ou le nom exact entre crochets), remplacez par rien (chaîne vide).
Exécutez sur Classeur entier et sur les Formules.

Cette méthode garde les formules, mais suppose une structure identique (mêmes feuilles, mêmes plages) entre source et destination.

Où se cachent les liaisons externes ?

Élément	Exemple de lien	Comment l’inspecter	Correction typique
Cellules (formules)	`=SOMME.SI.ENS('[Budget.xlsx]Ventes'!$D:$D;A2;…)`	Rechercher « [ » dans les formules	Remplacer `[Budget.xlsx]` par vide si la structure est identique, sinon Modifier la source
Noms définis	`=('[Repo.xlsx]Ref'!$A:$A)`	Formules → Gestionnaire de noms	Redéfinir vers des plages locales ou supprimer
Graphiques	`='[Source.xlsx]Feuil1'!$B$2:$B$100`	Sélectionner la série et vérifier la barre de formule	Re-sélectionner une plage du classeur cible
Validation des données	`=ListePrix` où `ListePrix` est un nom externe	Données → Validation des données → Paramètres	Remplacer par une plage/nombre locale ou un nom local
Mises en forme conditionnelles	Formule `=NB.SI('[Ancien.xlsx]Feuil1'!$A:$A;A2)>0`	MFC → Gérer les règles	Remplacer la formule par une version locale
Tableaux croisés/objets	Source de données externe ou cache pointant ailleurs	Analyse du TCD → Modifier la source de données	Recréer le TCD à partir des données locales

Automatiser le collage sans invites avec VBA

Si vous répétez l’opération régulièrement, une petite macro fiabilise le collage en désactivant temporairement les invites et en collant uniquement les valeurs.

' Coller les valeurs sans boîtes de dialogue
Option Explicit
Sub CollerValeurs_SansPrompts()
    Dim calcState As XlCalculation
    On Error GoTo Fin

    ' Sauvegarder l’état du calcul et accélérer
    calcState = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ' 1) Copier d’abord la plage source
    ' 2) Sélectionner la cellule en haut à gauche de la cible
    Selection.PasteSpecial Paste:=xlPasteValues

Fin:
    Application.CutCopyMode = False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.Calculation = calcState
End Sub

Important : veillez toujours à réactiver DisplayAlerts et à restaurer le mode de calcul dans un bloc Fin:. Évitez les macros qui basculent ces options sans les remettre en place.

Lister puis rompre les liaisons détectées

Ce script crée une feuille « Liens_Externes », liste chaque liaison (selon Excel) et permet de les rompre d’un clic. La rupture convertit les formules liées en valeurs ; travaillez sur une copie.

' Lister et rompre les liaisons externes du classeur actif
Option Explicit
Sub ListerEtRompreLiens()
    Dim arr, i As Long, ws As Worksheet, rep As VbMsgBoxResult
    On Error GoTo Fin

    arr = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    If IsEmpty(arr) Then
        MsgBox "Aucune liaison externe exposée par Excel.", vbInformation
        Exit Sub
    End If

    Application.ScreenUpdating = False
    On Error Resume Next
    Set ws = ActiveWorkbook.Worksheets("Liens_Externes")
    On Error GoTo 0
    If ws Is Nothing Then
        Set ws = ActiveWorkbook.Worksheets.Add
        ws.Name = "Liens_Externes"
    Else
        ws.Cells.Clear
    End If

    ws.Range("A1:D1").Value = Array("N°", "Lien (source)", "Action", "Statut")
    For i = LBound(arr) To UBound(arr)
        ws.Cells(i + 1, 1).Value = i
        ws.Cells(i + 1, 2).Value = CStr(arr(i))
        ws.Cells(i + 1, 3).Value = "Rompre ?"
    Next i
    ws.Columns.AutoFit

    rep = MsgBox("Rompre TOUTES les liaisons listées ? (Irreversible sur ces formules)", vbQuestion + vbYesNo)
    If rep = vbYes Then
        For i = LBound(arr) To UBound(arr)
            ActiveWorkbook.BreakLink Name:=CStr(arr(i)), Type:=xlLinkTypeExcelLinks
            ws.Cells(i + 1, 4).Value = "Rompue"
        Next i
        MsgBox "Terminé. Les formules liées ont été converties en valeurs.", vbInformation
    Else
        MsgBox "Aucune liaison rompue.", vbInformation
    End If

Fin:
    Application.ScreenUpdating = True
End Sub

Convertir massivement des références externes en locales

Si seule la partie classeur gêne ([Source.xlsx]) et que la même feuille/plage existe localement, cette macro remplace proprement dans la sélection et dans les noms définis.

' Remplacer [Source.xlsx] par vide dans la sélection et les noms
Option Explicit
Sub ConvertirLiensExternesEnLocaux()
    Dim nomClasseur As String, c As Range, nm As Name
    Dim avant As String, apres As String

    nomClasseur = InputBox("Nom exact du classeur à retirer (ex: Source.xlsx) :", "Remplacer les références")
    If Len(nomClasseur) = 0 Then Exit Sub

    avant = "[" & nomClasseur & "]"
    apres = ""

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' Cellules actives (formules)
    If TypeName(Selection) = "Range" Then
        For Each c In Selection.Cells
            If c.HasFormula Then
                c.Formula = Replace(c.Formula, avant, apres, 1, -1, vbTextCompare)
            End If
        Next c
    End If

    ' Noms définis
    For Each nm In ActiveWorkbook.Names
        If InStr(1, nm.RefersTo, avant, vbTextCompare) > 0 Then
            nm.RefersTo = Replace(nm.RefersTo, avant, apres, 1, -1, vbTextCompare)
        End If
    Next nm

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    MsgBox "Remplacements effectués. Vérifiez les formules signalées par #REF! le cas échéant.", vbInformation
End Sub

Conseil : exécutez d’abord sur une petite plage représentative pour valider que les feuilles et plages existent bien côté destination.

Astuces et cas particuliers

Noms de feuille « fragiles » : une feuille débutant par un chiffre, contenant des espaces, caractères spéciaux ou des apostrophes mal appariées peut compliquer le remplacement. Renommer 2011 Files en Files2011 simplifie souvent la syntaxe, mais ce n’est pas la cause la plus fréquente de l’alerte.
Option globale à l’ouverture : Fichier → Options → Avancé → cocher/décocher « Demander la mise à jour des liaisons automatiques ». Cette option agit surtout à l’ouverture des fichiers, peu pendant un collage, mais elle réduit d’autres invites.
Chemins de fichiers : si vos liens pointent vers un lecteur mappé (ex. Z:\) alors que vos collègues utilisent un chemin UNC (ex. \\Serveur\Partage), Excel peut croire que la source a changé et redemander l’emplacement. Un Modifier la source vers un chemin commun stabilise les liens.
Objets collés à votre insu : si l’alerte persiste malgré un collage en valeurs, c’est souvent qu’un objet (graphique, forme, segment) a été copié. Refaire le collage en Valeurs (pas « Conserver le formatage »), ou utilisez Accueil → Rechercher et sélectionner → Atteindre spécial → Objets pour les identifier et les supprimer.
Performance : sur >500 lignes, passer temporairement en Calcul manuel et coller par blocs (ex. 50 000 cellules à la fois) évite les longues pauses dues à la recalcul global.
Ouverture silencieuse des sources dans un script : si vous pilotez l’opération en VBA, vous pouvez ouvrir les sources avec Workbooks.Open Filename:=..., UpdateLinks:=0, coller, puis repointer/rompre les liens au calme.

Procédure de diagnostic pas à pas

Test éclair : collez la même plage en Valeurs uniquement. Si l’alerte disparaît, vous avez confirmé le problème de liaisons.
Rechercher « [ » dans tout le classeur, ciblage Formules. Listez les feuilles incriminées.
Vérifier le ruban Données : si Modifier les liaisons est visible, ouvrez‑le et regardez la liste des sources. Évaluez : conserver, repointer, ou rompre.
Contrôler les zones « silencieuses » : Gestionnaire de noms, MFC, Validation, Graphiques. Corriger ou localiser.
Si beaucoup de liens : exécutez ListerEtRompreLiens pour obtenir une vue d’ensemble et décider.
Stabiliser : (optionnel) convertir les liens externes en locaux par remplacement ciblé ou en valeurs si le modèle ne nécessite plus ces dépendances.

FAQ rapide

Est‑ce un bug ?
Non. « Update Values » est une demande normale d’Excel quand il doit résoudre une référence vers un fichier introuvable/non ouvert. Avec de nombreux liens, l’invite se répète pour chacun.

Pourquoi ça s’intensifie au‑delà de 500 lignes ?
Parce que le volume augmente le nombre de formules/objets touchés et multiplie les tentatives de résolution. Le calcul automatique peut aussi rallonger chaque étape.

Coller en valeurs est‑il la seule solution ?
Non. Vous pouvez ouvrir la source, Modifier la source pour repointer proprement, Rompre les liaisons après collage, ou convertir les références externes en locales si la structure est identique.

Que fait « Rompre les liaisons » ?
Excel remplace les formules liées par leurs valeurs calculées au moment de la rupture. C’est irréversible (à moins d’annuler immédiatement), d’où l’intérêt de travailler sur une copie.

Peut‑on empêcher l’invite sans perdre les formules ?
Oui, en ouvrant d’abord les sources et en repointant les liens vers le classeur cible. Sinon, automatisez le collage et le nettoyage dans un script contrôlé.

Exemples de formules qui déclenchent « Update Values »

=RECHERCHEV(A2; '[Tarifs.xlsx]Feuil1'!$A:$D; 4; FAUX)
=SOMME.SI.ENS('[Budget.xlsx]Ventes'!$G:$G; $B:$B; "FR")
=SI(NB.SI('[Clients.xlsx]Base'!$A:$A; A2)&gt;0; "Existant"; "Nouveau")
='C:\Finance\[Pilotage.xlsx]Synthèse'!$B$2

Dans chacun de ces cas, ouvrez « Tarifs.xlsx », « Budget.xlsx », etc. avant de coller, ou convertissez ces références pour qu’elles pointent vers des données locales.

Bonnes pratiques pour éviter le problème à l’avenir

Limiter les dépendances : privilégiez des tables et noms locaux regroupés dans un même classeur, ou un entrepôt unique (ex. un seul classeur « Données »).
Nommer proprement : noms courts, sans espaces, sans caractères spéciaux (ex. Ventes2025 plutôt que Ventes 2025 €).
Préparer les collages massifs : passez en calcul manuel, ouvrez les sources, puis collez. Reprenez le calcul automatique après contrôle.
Documenter les liens : gardez une feuille « Dépendances » listant les principales sources. Les macros de listing ci‑dessus aident à l’actualiser.

Résumé

La boîte « Update Values » qui se répète au collage est le symptôme de liaisons externes non résolues. Pour un résultat immédiat, collez en Valeurs. Pour conserver vos formules, ouvrez la source et reconfigurez les liens (Modifier la source, Rompre les liaisons ou remplacement ciblé). Pour les opérations récurrentes, appuyez‑vous sur une macro qui désactive les invites le temps du collage et, si nécessaire, liste/romp les liaisons en fin de traitement. En combinant ces approches, vous copierez des blocs >500 lignes entre classeurs sans boîtes de dialogue intempestives… et sans mauvaise surprise.