Copier une feuille de calcul Excel et maintenir la référence relative des cellules dans les formules

J'ai trouvé plus facile, dans de nombreux cas, de faire ce qui suit :

• copier la feuille dans un nouveau classeur
• activer la nouvelle feuille dans le nouveau classeur
• sélectionner tout (Ctrl+A)
• faire une recherche/remplacement sur
• trouver : [WorkbookA.xlsx]!
• remplacer : <laisser vide>
• remplacer tout

Essayez d'utiliser Ctrl + ~ pour afficher les formules. Utilisez ensuite Ctrl + A pour tout sélectionner, copiez et collez dans le bloc-notes.

Enfin, copiez hors du bloc-notes et collez dans votre autre cahier.

La réponse non signée juste en dessous de celle-ci est celle qui a fonctionné pour moi, avec une très légère variation.

1. Créer et enregistrer une feuille de calcul de destination.

2. Utilisez “déplacer”, “copier”, ou faites glisser votre page avec les formules dans la nouvelle feuille de calcul. Les formules de la nouvelle page restent ainsi pointées vers l'ancienne feuille de calcul. Enregistrez ensuite la nouvelle feuille de calcul au même endroit que l'ancienne.

3. Ensuite, allez dans l'onglet “Données” > cliquez sur “Modifier les liens”. L'option ne sera active que s'il y a des liens dans la page.

4. Dans la boîte de dialogue qui s'affiche, sélectionnez le nom du fichier source et cliquez sur “Modifier la source”.

5. Dans la boîte de dialogue d'ouverture de fichier qui s'affiche ensuite, sélectionnez le nom de la nouvelle feuille de calcul.

Cliquez sur “Fermer” et vous avez terminé.

Ou bien faites simplement ce qui suit :

Convertissez ceci :

=database_feed!A1

en ceci :

=INDIRECT("database_feed!A1")

et ne changez plus vos références lorsque vous copiez entre les feuilles.

Si vous n'avez pas beaucoup de feuilles référencées, une autre alternative serait d'utiliser

=INDIRECT("'"&B1&"'!A1")

et d'entrer le nom de la feuille de référence dans la cellule B1. Vous n'avez alors plus qu'une seule cellule à mettre à jour lorsque vous copiez la nouvelle feuille de calcul.

Le code ci-dessous peut être adapté à vos besoins. Il reprend toutes les formules de la fiche sur wb1 et les applique à une fiche dans un nouveau cahier. Les formules sont appliquées comme Strings, il n'y a donc pas d'insertion de références au cahier original. De plus, ce code est super rapide car il n'utilise pas le presse-papiers et ne nécessite pas de bouclage de cellules.

Sub copyformulas()

Dim wb1 As Workbook, wb2 As Workbook
Dim s1 As Worksheet, s2 As Worksheet
Dim formArr() As Variant

Set wb1 = ThisWorkbook
Set s1 = wb1.Sheets("Sheet1")
Set wb2 = Workbooks.Add
Set s2 = wb2.Sheets("Sheet1")

formArr = s1.UsedRange.Formula
s2.Range("A1").Resize(UBound(formArr, 1), UBound(formArr, 2)).Formula = formArr

End Sub

Comme 99% des réponses ne répondaient même pas à la question initiale, voici la réponse appropriée.

1. Copiez les feuilles du fichier original (Original.xlsx) dans le nouveau fichier Excel (New.xlsx) comme vous le feriez normalement. En général, je fais un clic droit sur le nom et je choisis “Déplacer ou copier…”.

2. Enregistrez le deuxième fichier - nouvellement créé (New.xlsx).

3. Dans le nouveau fichier, sous “Données”, cliquez sur “Modifier les liens”

4. Dans le pop-up, choisissez “Modifier la source…”

5. Localisez le fichier (New.xlsx) et cliquez sur “Ouvrir”.

Toutes les références à l'original (Original.xlsx) seront supprimées.

C'EST FAIT !

• copier la feuille de calcul dans le “WorkbookB.xlsx”
• ouvrir la feuille de calcul dans le nouveau fichier
• sélectionner tout
• aller dans le menu Données, cliquer sur Modifier les liens
• modifier les liens de sorte que le lien vers l'ancien fichier soit maintenant un lien vers le fichier actuellement ouvert

Cela fonctionne pour moi.

Si vous devez le faire automatiquement parce que vous tirez des feuilles dans un programme VBA. Utilisez ceci :

Public Sub ChangeSource()
'
' ChangeSource Macro
' Edit the links to point to the current workbook.
'
    Dim allLinks As Variant
    allLinks = ThisWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(allLinks) Then
        Dim eachLink As Long
        For eachLink = 1 To UBound(allLinks)
            If InStr(3, "String found in source book name.", allLinks(eachLink)) Then
                ThisWorkbook.ChangeLink Name:=allLinks(eachLink), NewName:=ThisWorkbook.FullName, Type:=xlExcelLinks
            End If
        Next eachLink
    End If
End Sub

Changez simplement “String found in source book name.” pour correspondre aux anciens liens que vous voulez remplacer. Vous pouvez supprimer ce bloc si vous voulez remplacer tous les liens.

Autre “astuce” : avant de copier la feuille de calcul source, remplacez tous les qualificatifs de formule = par d'autres jeux de caractères (par exemple ###=).

Copiez la feuille de calcul, puis après la copie, remplacez le qualificatif de formule (en remplaçant ###= par =).

Assurez-vous que toutes les références de feuilles dans les formulaires sont également copiées sur la nouvelle feuille avant la feuille de référence.

Les deux cahiers d'exercices doivent être ouverts pour que cela fonctionne. Vous exécutez cette macro et elle copiera workbookA!sheet1 à workbookB!sheet1 et remplacera ensuite toutes les références de workbookA. C'est brut, mais ça marche. Vous pouvez évidemment changer le code pour qu'il corresponde aux noms de vos WorkbookA.xlsx, mais assurez-vous qu'ils aient la bonne extension et restent entre guillemets.

_Oh, pour faire une macro, au cas où vous ne le sauriez pas, appuyez sur alt + F11 pour faire apparaître l'éditeur Visual Basic. Ensuite, faites un clic droit sur WBA insert - module et copiez-collez le code ci-dessous dans le module. Appuyez ensuite sur F5 pour lancer la macro. Si la macro ne s'exécute pas, c'est probablement parce que les macros ne sont pas activées, alors allez-y, enregistrez-la et réouvrez et lorsque le système vous demande d'activer les macros, activez-les.

J'avais un problème similaire. La raison pour laquelle les formules ont été collées avec le lien vers WBA est que l'onglet (feuille) sur lequel je travaillais dans WBA était nommé différemment de celui de WBB. Pour moi, c'était toujours “la dernière feuille” mais l'une s'appelait “MinFlow” et l'autre “NormalFlow”. J'ai renommé les deux feuilles “Résultats” et le copier/coller a fonctionné comme je le voulais - un “collage relatif”.

Sélectionnez les cellules que vous souhaitez déplacer. Maintenant, essayez de les déplacer par glisser-déposer vers une autre feuille de travail (onglet différent).

Je sais, ça défile. Voici la partie délicate : il suffit d'appuyer sur cmd (mac) ou alt (win) et cela vous permettra de déposer les cellules dans un autre onglet.

Par exemple, si la première formule est =J2 et que celle-ci devient =[filepath]J2, il suffit de faire une recherche et un remplacement de toute la nouvelle feuille de calcul pour [filepath] et de remplacer par rien. Ceci l'efface et restaure la formule à =J2.

Pas de VB nécessaire !

Voici une solution simple à ce problème :

1. Copiez sur les cellules comme d'habitude.
2. Dans la formule, sélectionnez et copiez le texte qui relie au manuel précédent [WorkbookA.xlsx].
3. Sélectionnez toutes les cellules que vous voulez modifier et appuyez sur CTRL+F et sélectionnez l'onglet de remplacement.
4. Remplacez [WorkbookA.xlsx] par un espace vide (c'est-à-dire n'écrivez rien dans le champ Replace with, appuyez sur Replace All.

Voilà - c'est fait.

1. Copiez la feuille comme d'habitude. (clic droit sur la tabulation et aller à “déplacer ou copier”) C'est pour le formatage.

2. Copiez toutes les cellules de la feuille originale (en utilisant Ctrl+A ou le triangle en haut à gauche et Ctrl+C)

3. Coller comme valeurs dans le nouveau classeur (sur la feuille “step-1”) (Options de collage>123)

Ouvrez les deux workbooks.&nbsp ; Dans le workbook source (WorkbookA.xlsx), sélectionnez la feuille que vous voulez copier.&nbsp ; Faites un clic droit sur l'onglet de la feuille et sélectionnez “Move or Copy…”.&nbsp ; Dans la boîte de dialogue “Move or Copy”, sélectionnez “WorkbookB.xlsx” dans la liste déroulante “To book”, choisissez l'endroit où vous voulez le mettre dans ce book, et cochez “Create a copy”.&nbsp ; (Et cliquez sur “OK”.)

Faites une copie de la feuille que vous voulez déplacer, c'est-à-dire le classeur A.xlsx. Renommez-la en disant “Copie du classeur A.xlsx”. Ouvrez maintenant ce nouveau classeur ainsi que le classeur dans lequel vous souhaitez déplacer la feuille, qui dans ce cas sera le classeur B.xlsx. Cliquez avec le bouton droit de la souris sur les feuilles du classeur que vous avez créé, c'est-à-dire Copie du classeur A.xlsx, et sélectionnez “déplacer ou copier”, puis déplacez ces feuilles vers le classeur B.xlsx. Vous avez terminé !