Désactiver le changement automatique de la référence de la cellule D'Excel après copier / coller

une solution très simple est de sélectionner la gamme que vous souhaitez copier, puis de trouver et de remplacer (Ctrl + h), en remplaçant = d'un autre symbole qui n'est pas utilisé dans votre formule (par exemple,#) - l'empêchant ainsi d'être un actif de formule.

Ensuite, copiez et collez la plage sélectionnée pour son nouvel emplacement.

Enfin, Rechercher et Remplacer pour modifier # retour = à la fois dans la gamme originale et dans la nouvelle, rétablissant ainsi les deux gammes à être des formules de nouveau.

je suis venu sur ce site à la recherche d'un moyen facile de copier sans changer les références de cellules. Mais maintenant je pense que ma propre solution est plus simple que la plupart de ces méthodes. Ma méthode repose sur le fait que copier change les références mais pas déplacer. Voici un exemple simple.

supposons que vous avez des données brutes dans les colonnes A et B, et une formule dans C (par exemple C=A+B) et vous voulez la même formule dans la colonne F mais copier de C à F conduit à F = D+E.

1. copier le contenu de C à n'importe quel temporaire vide de la colonne, disons R. Les références relatives va changer pour R=P+Q, mais ignorer ce même si elle est considérée comme une erreur.
2. revenir à C et déplacer (pas copier) il à F. La formule doit être inchangée donc F=A+B.
3. maintenant allez à R et copiez - le à nouveau à C. La référence Relative reviendra à C=A+B
4. supprimer la formule temporaire dans R.
5. Bob est votre oncle.

j'ai fait cela avec une gamme de cellules donc j'imagine que ça marcherait avec pratiquement n'importe quel niveau de complexité. Vous avez juste besoin d'une zone vide pour garer les cellules coupées. Et bien sûr, tu dois te rappeler où tu les as laissées.

cette astuce simple fonctionne: copier et coller. Ne pas couper et coller. Après avoir collé, puis resélectionner la partie que vous avez copiée et aller à modifier, glisser vers le bas pour effacer, et effacer le contenu.

je pense que vous êtes coincé avec le contournement que vous avez mentionné dans votre édition.

je commencerais par Convertir chaque formule sur la feuille en texte à peu près comme ceci:

Dim r As Range

For Each r In Worksheets("Sheet1").UsedRange
    If (Left$(r.Formula, 1) = "=") Then
        r.Formula = "'ZZZ" & r.Formula
    End If
Next r

'ZZZ utilise ' pour signifier une valeur de texte et le ZZZ comme une valeur que l'on peut regarder quand on veut convertir le texte à la formule. Évidemment si l'une de vos cellules commence réellement avec le texte ZZZ alors changez le ZZZ valeur dans la macro VBA à autre chose

Lors de la réorganisation terminée, je puis convertir le texte d'une formule comme ceci:

For Each r In Worksheets("Sheet1").UsedRange
    If (Left$(r.Formula, 3) = "ZZZ") Then
        r.Formula = Mid$(r.Formula, 4)
    End If
Next r

un vrai inconvénient de cette méthode est que vous ne pouvez pas voir les résultats d'aucune formule pendant que vous réorganisez. Vous pouvez trouver que lorsque vous convertissez de nouveau du texte à la formule que vous avez une masse de #REF erreurs par exemple.

il pourrait être bénéfique de travailler sur ce point par étapes et de revenir à des formules de temps en temps pour vérifier qu'aucun catastrophes ont eu lieu

il est courant d'utiliser find/ replace pour désactiver des formules tout en effectuant des manipulations. Par exemple, copier avec transposer. La formule est désactivée en plaçant un placeholder (par exemple "$=") devant elle. Rechercher remplacer pouvez vous débarrasser de ces une fois la manipulation terminée.

cette vba automatise simplement la recherche/ remplacement de la feuille active.

' toggle forumlas on active sheet as active/ inactive
' by use of "$=" prefix

Sub toggle_active_formulas()
    Dim current_calc_method As String
    initial_calc_method = Application.Calculation
    Application.Calculation = xlCalculationManual
    Dim predominant As Integer
    Dim c As Range
    For Each c In ActiveSheet.UsedRange.Cells
        If c.HasFormula Then
            predominant = predominant + 1
        ElseIf "$=" = Left(c.Value, 2) Then
            predominant = predominant - 1
        End If
    Next c
    If predominant > 0 Then
        For Each c In ActiveSheet.UsedRange.Cells
            On Error Resume Next
            If c.HasFormula Then
                c.Value = "$" & c.Formula
            End If
        Next c
    Else
        For Each c In ActiveSheet.UsedRange.Cells
            On Error Resume Next
            If "$=" = Left(c.Value, 2) Then
                c.Formula = Right(c.Value, Len(c.Value) - 1)
            End If
        Next c
    End If
    Application.Calculation = initial_calc_method
End Sub

Essayez ceci: Faites un clic gauche sur l'onglet, faites une copie de la feuille entière, puis coupez les cellules que vous voulez garder les mêmes et collez-les dans votre feuille de travail originale.

j'ai trouvé une autre solution qui est très simple: 1. Couper le contenu 2. Coller dans le nouvel emplacement 3. Copiez le contenu que vous venez de coller dans le nouvel emplacement vous le souhaitez. 4. Annulez l'opération couper-coller, remettez le contenu original là où vous l'avez eu. 5. Coller le contenu du presse-papiers au même endroit. Ces contenus auront les références originales.

il semble beaucoup, mais est super rapide avec des raccourcis clavier: 1. Ctrl-x, 2. Ctrl-v, 3. Ctrl-c, 4. Ctrl-z, 5. Ctrl-v

N'ont pas vérifié dans Excel, mais cela fonctionne dans Libreoffice4:

tout le truc de réécriture d'adresse vient pendant

(a1) couper

(a2) coller

vous devez interrompre la consécration en mettant quelque chose entre les deux:

(b1) couper

(b2) sélectionnez les cellules vides (plus de 1) et faites-la glisser(déplacer)

(b3) pâte

l'Étape (b2) est où la cellule qui est sur le point de se mettre à jour arrête le tracking. Rapide et simple.

Cliquez sur la cellule que vous souhaitez copier. Dans la barre de formule, mettez en surbrillance la formule.

Appuyez sur Ctrl C.

Appuyez sur la touche echap (pour vous sortir de activement l'édition de cette formule).

choisir une nouvelle cellule. Ctrl V.

solution simple que j'ai utilisée tout à l'heure dans une situation similaire:

1. Faire une copie de la feuille de calcul
2. Couper + Coller à partir de la feuille de travail dupliquée
3. supprimer la feuille de travail en double pour nettoyer

vos références cellulaires sont maintenant copiées sans être modifiées.