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

• copier la feuille de calcul dans '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 manière à ce que le lien vers l'ancien fichier soit désormais un lien vers le fichier de la nouvelle version. fichier actuellement ouvert

Cela fonctionne pour moi.

Le code ci-dessous peut être adapté à vos besoins. Il reprend toutes les formules de la feuille sur wb1 et les applique à une feuille dans un nouveau classeur. Les formules sont appliquées en tant que String de sorte qu'il n'y a pas d'insertion de références au classeur d'origine. De plus, ce code est très rapide car il n'utilise pas le presse-papiers et ne nécessite pas de boucles dans les 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

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

Il suffit de modifier "String found in source book name." pour qu'il corresponde aux anciens liens que vous souhaitez remplacer. Vous pouvez supprimer ce bloc si vous souhaitez remplacer tous les liens.

Les deux classeurs doivent être ouverts pour que cela fonctionne. . Vous exécutez cette macro et elle copiera workbookA!sheet 1 à workbookB!sheet1 puis remplacer tous les workbookA des références. C'est rudimentaire, mais ça marche . Vous pouvez évidemment modifier le code pour qu'il corresponde à vos noms WorkbookA.xlsx, mais veillez à ce qu'ils aient l'extension correcte et qu'ils 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. Cliquez ensuite avec le bouton droit de la souris sur WBA insert - module et copier-coller le code ci-dessous dans le module. Ensuite, appuyez sur F5 pour exécuter la macro. Si la macro ne s'exécute pas, c'est probablement parce que les macros ne sont pas activées, alors enregistrez-la et ouvrez-la à nouveau, et lorsqu'elle vous demande d'activer les macros, activez-les.

Sub copysheetremoveWBref()

    Application.ScreenUpdating = False

    'activate WBA
    Application.Workbooks("workbooka.xlsx").Activate
    'Select WBA Sheet1
    Application.Workbooks("workbooka.xlsx").Sheets("Sheet1").Select
    'copy WBA!sheet1 to WBB!sheet1
    Sheets("Sheet1").copy Before:=Workbooks("WorkbookB.xlsx").Sheets("sheet2")
    'find WBA references and remove them
    Cells.Replace What:="=[workbookA.xlsx]", Replacement:="=", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Application.ScreenUpdating = True

End Sub

Pour ce faire, j'ai copié les cellules dans la nouvelle feuille de calcul comme d'habitude, puis j'ai effectué une recherche et un remplacement pour supprimer l'ancien chemin d'accès au fichier dans les formules.

Par exemple, si la première formule est =J2 et cela devient =[filepath]J2 Il suffit ensuite de rechercher et de remplacer l'ensemble de la nouvelle feuille de calcul par les éléments suivants [filepath] et remplacer par rien. Cela le supprime et rétablit la formule à =J2 .

Pas besoin de VB !