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

Из http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_form_a_a_a_a_a_a_a

Или из http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_formulas/range а>:

If you're a VBA programmer, you can simply execute the following code: 
With Sheets("Sheet1")
 .Range("A11:D20").Formula = .Range("A1:D10").Formula
End With

Очень простое решение - выбрать диапазон, который вы хотите скопировать, а затем найти и заменить (Ctrl + h), заменив = на другой символ, который не используется в вашей формуле (например, #) - таким образом, он не станет активной формулой.

Затем скопируйте и вставьте выбранный диапазон в новое место.

Наконец, найдите и замените, чтобы изменить # обратно на = как в исходном, так и в новом диапазоне, таким образом снова восстанавливая оба диапазона как формулы.

Я пришел на этот сайт в поисках простого способа копирования без изменения ссылок на ячейки. Но теперь я думаю, что мой собственный обходной путь проще, чем большинство из этих методов. Мой метод основан на том, что 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. Боб твой дядя.

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

Этот простой трюк работает: копировать и вставлять. НЕ вырезать и вставлять. После вставки повторно выберите скопированную часть и перейдите к РЕДАКТИРОВАТЬ, сдвиньте вниз до ОЧИСТИТЬ и ОЧИСТИТЬ СОДЕРЖАНИЕ.

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

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

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 ошибок.

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

Обычно для отключения формул во время выполнения манипуляций используют поиск / замену. Например, копировать с транспонированием. Формула отключается, если перед ней помещается заполнитель (например, "$ ="). Найти замену можно, чтобы избавиться от них после завершения манипуляции.

Эта vba просто автоматизирует поиск / замену активного листа.

' 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

Этот макрос выполняет всю работу.

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

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

Я нашел это решение, которое автоматизирует решение @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

Простой обходной путь, который я использовал только что в похожей ситуации:

1. Сделайте копию рабочего листа.
2. Вырезать + Вставить ячейки с формулами из повторяющегося листа (например, Copy of Sheet1) в исходный лист.
3. Удалите все вхождения имени дублирующего листа из вновь вставленных формул; например найти и заменить все вхождения поискового запроса 'Copy of Sheet1'! пустой строкой (т. е. пустой).
4. Удалите повторяющийся лист для очистки.

Примечание: если в названии вашего листа отсутствуют пробелы, вам не нужно использовать одинарную кавычку / апостроф (').

Ваши ссылки на ячейки теперь копируются без изменений.

Попробуйте следующее: щелкните вкладку левой кнопкой мыши, сделав копию всего листа, затем вырежьте ячейки, которые вы хотите сохранить, и вставьте их в исходный рабочий лист.

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

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

Не проверял в Excel, но это работает в Libreoffice4:

Вся процедура перезаписи адресов происходит во время последовательного
(a1) вырезать
(a2) вставить

Вам нужно прервать последовательность, вставив что-то среднее:
(b1) вырезать
(b2) выбрать несколько пустых ячеек (более 1) и перетащить (переместить) их
(b3) вставить

Шаг (b2) - это когда ячейка, которая собирается обновиться, останавливает отслеживание. Быстро и просто.

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

Нажмите Ctrl C.

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

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