Как отключить автоматическое изменение ссылки на ячейки в Excel после копирования / вставки?

Нажмите на ячейку, которую хотите скопировать. В строке формул выделите формулу.

Нажмите Ctrl C.

Нажмите escape (чтобы вывести вас из активного редактирования этой формулы).

Выберите новую ячейку. Ctrl V.

Я нашел другой обходной путь, который очень прост: 1. Вырежьте содержимое 2. Вставьте их в новое место 3. Скопируйте содержимое, которое вы только что вставили, в новое место, которое вы хотите. 4. Отмените операцию Cut-Paste, поместив оригинальное содержимое туда, где вы его получили. 5. Вставьте содержимое из буфера обмена в то же место. Это содержание будет иметь оригинальные ссылки.

Это выглядит много, но очень быстро с сочетаниями клавиш: 1. Ctrl-x, 2. Ctrl-v, 3. Ctrl-c, 4. Ctrl-z, 5. Ctrl-v

Я нашел это решение, которое автоматизирует @Alistair решение Collins.

В основном Вы изменитесь = в любой формуле к *, тогда делают вставку после этого, Вы измените ее назад

        Dim cell As Range

msgResult = MsgBox("Yes to lock" & vbNewLine & "No unlock ", vbYesNoCancel + vbQuestion, "Forumula locker")

If msgResult = vbNo Then
    For Each cell In Range("A1:i155")
        If InStr(1, cell.Value, "*") > 0 Then
            cell.Formula = Replace(cell.Formula, "*", "=")
        End If
    Next cell
ElseIf msgResult = vbYes Then
    For Each cell In Range("A1:i155")
        If cell.HasFormula = True Then
            cell.Formula = Replace(cell.Formula, "=", "*")
        End If
    Next cell
End If

Я думаю, что вы застряли с обходной путь, который вы упомянули в своем редактировании.

Я бы начал с преобразования каждой формулы на листе в текст, примерно такой:

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 использует ' для обозначения текстового значения, а ZZZ как значение, которое мы можем искать, когда хотим преобразовать текст обратно в формулу. Очевидно, что если любая из ваших ячеек фактически начинается с текста ZZZ, то измените значение ZZZ в макросе VBA на что-то другое

Когда перестановка будет завершена, я бы затем преобразовал текст обратно в формула, подобная этой:

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

Одним из недостатков этого метода является то, что вы не можете видеть результаты какой-либо формулы, пока вы реорганизуете. Например, вы можете обнаружить, что при обратном преобразовании из текста в формулу вы получаете множество ошибок #REF.

Может быть полезно поработать над этим поэтапно и время от времени возвращаться к формулам, чтобы убедиться, что никаких катастроф не произошло

Я пришел на этот сайт в поисках простого способа копирования без изменения ссылок на ячейки. Но теперь я думаю, что мой собственный обходной путь проще, чем большинство этих методов. Мой метод основан на том факте, что Copy изменяет ссылки, а Move - нет. Вот простой пример.

Предположим, у вас есть необработанные данные в столбцах A и B и формула в C (например, C = A + B), и вы хотите использовать ту же формулу в столбце F, но копирование из C в F приводит к F = D + E.

1. Скопируйте содержимое C в любой пустой временный столбец, скажем R. Относительные ссылки изменится на R = P + Q, но игнорируют это, даже если он помечен как ошибка.
2. Вернитесь в C и переместите (не копируйте) в F. Формула должна быть неизменной, поэтому F = A + B.
3. Теперь перейдите к R и скопируйте его обратно в C. Относительная ссылка вернется к C = A + B
4. Удалите временную формулу в R.
5. Боб твой дядя.

Я сделал это с помощью ряда ячеек, поэтому я думаю, что он будет работать практически с любым уровнем сложности. Вам просто нужно пустое место для парковки скрученных клеток. И, конечно же, вы должны помнить, где вы их оставили.

Этот макрос делает всю работу.

Sub Absolute_Reference_Copy_Paste()
'By changing "=" in formulas to "#" the content is no longer seen as a formula.
' C+S+e (my keyboard shortcut)

Dim Dummy As Range
Dim FirstSelection As Range
Dim SecondSelection As Range
Dim SheetFirst As Worksheet
Dim SheetSecond As Worksheet

On Error GoTo Whoa

Application.EnableEvents = False

' Set starting selection variable.
Set FirstSelection = Selection
Set SheetFirst = FirstSelection.Worksheet

' Reset the Find function so the scope of the search area is the current worksheet.
Set Dummy = Worksheets(1).Range("A1:A1").Find("Dummy", LookIn:=xlValues)

' Change "=" to "#" in selection.
Selection.Replace What:="=", Replacement:="#", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

' Select the area you want to paste the formulas; must be same size as original
  selection and outside of the original selection.
Set SecondSelection = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
Set SheetSecond = SecondSelection.Worksheet

' Copy the original selection and paste it into the newly selected area. The active
  selection remains FirstSelection.
FirstSelection.Copy SecondSelection

' Restore "=" in FirstSelection.
Selection.Replace What:="#", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

' Select SecondSelection.
SheetSecond.Activate
SecondSelection.Select

' Restore "=" in SecondSelection.
Selection.Replace What:="#", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

' Return active selection to the original area: FirstSelection.
SheetFirst.Activate
FirstSelection.Select

Application.EnableEvents = True

Exit Sub

Whoa:
' If something goes wrong after "=" has been changed in FirstSelection, restore "=".
FirstSelection.Select
Selection.Replace What:="#", Replacement:="=", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End Sub

Обратите внимание, что вы должны соответствовать размеру и форме исходного выделения при создании нового выбора.