Скопируйте таблицу Excel и сохраните относительную ссылку на ячейку в формулах

Question

Еще одна проблема копирования в Excel:

Как я могу скопировать рабочий лист из WorkbookA.xlsx в WorkbookB.xlsx без того, чтобы скопированный рабочий лист все еще ссылался на WorkbookA.xlsx, например, формула =B!23 становится =[WorkbookA.xlsx]!B!23 при копировании.

Я хочу сохранить "относительные" ссылки на ячейки вместо "абсолютных" ссылок на ячейки (я придумаю эту терминологию в мире Excel, если она еще не существует).

Другой возможной альтернативой, которую я не могу заставить это работать, является возможность вставлять только "значения" ячейки. Excel обрабатывает "значения" как вычисленные значения, а не фактические формулы в ячейке. Если я выберу формулу пасты, она все равно дает абсолютные ссылки.

Подробнее о том, зачем мне это нужно: у меня есть рабочий xlsx для ежедневных операций. Нам постоянно нужно "обновлять" этот xlsx, и поэтому один человек может создать копию и свои изменения для одного листа. Одновременно другой человек может также вносить изменения в другой лист. Учитывая, что эти листы не имеют зависимых ячеек на других листах, таких как сводный отчет, нам желательно просто скопировать и объединить листы в исходный xlsx. Но "абсолютные" ссылки дают много хлопот.

Answer 1

Во многих случаях мне было проще сделать следующее:

• скопируйте лист в новую рабочую книгу
• активировать новый лист в новой книге
• выбрать все (Ctrl+A)
• найти / заменить на
  • найти: [WorkbookA.xlsx]!
  • заменить: <оставить пустым>
• заменить все

Answer 2

Попробуйте использовать Ctrl + ~ для отображения формул. Затем используйте Ctrl + A, чтобы выделить все, скопируйте его и вставьте в блокнот.

Наконец, скопируйте его из блокнота и вставьте в другую рабочую книгу.

Answer 3

Неподписанный ответ прямо под этим - тот, который работал для меня, с очень небольшим изменением.

1. Создать и сохранить таблицу назначения.

2. Используйте "переместить", "скопировать" или перетащите страницу с формулами в новую электронную таблицу. Это оставляет формулы на новой странице, указывающие на старый лист. Затем сохраните новую электронную таблицу в том же месте, что и старая таблица.

3. Затем перейдите на вкладку "Данные"> нажмите "Изменить ссылки". Опция не будет активной, если на странице нет ссылок.

4. В появившемся диалоговом окне выберите имя исходного файла и нажмите "Изменить источник".

5. В открывшемся диалоговом окне открытия файла выберите имя новой электронной таблицы.

Нажмите Закрыть и все готово.

Answer 4

Или просто сделайте следующее:

Преобразуйте это:

=database_feed!A1

к этому:

=INDIRECT("database_feed!A1")

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

Если на вас не ссылаются многие листы, другой альтернативой будет использование

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

и введите имя справочного листа в ячейку B1. Теперь у вас есть только одна ячейка для обновления при копировании в новую электронную таблицу.

Answer 5

Код ниже может быть адаптирован к вашим потребностям. Он берет все формулы из листа на wb1 и применяет их к листу в новой книге. Формулы применяются в виде String , поэтому ссылки на исходную книгу не вставляются. Кроме того, этот код очень быстрый, потому что он не использует буфер обмена и не требует циклического перемещения по ячейкам.

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

Answer 6

• скопировать лист в «WorkbookB.xlsx»
• открыть лист в новом файле
• выбрать все
• перейти в меню «Данные», нажать на ссылку «Изменить»
• отредактируйте ссылки так, чтобы ссылка на старый файл теперь была ссылкой на открытый в данный момент файл

Это работает для меня.

Answer 7

Поскольку 99% ответов даже не касались первоначального вопроса, вот правильный ответ.

1. Скопируйте листы из исходного файла (Original.xlsx) в новый файл Excel (New.xlsx), как обычно. Обычно я щелкаю правой кнопкой мыши на имени и выбираю «Переместить или Скопировать ...».

2. Сохраните второй - вновь созданный файл (New.xlsx).

3. В новом файле в разделе "Данные" нажмите "Редактировать ссылки".

4. Во всплывающем окне выберите «Изменить источник ...»

5. Найдите файл (New.xlsx) и нажмите Открыть.

Все ссылки на оригинал (Original.xlsx) будут удалены.

СДЕЛАННЫЙ!

Answer 8

Привет, вот простое решение этой проблемы:

1. Скопируйте ячейки как обычно.
2. В формуле выберите и скопируйте текст, связывающий его с предыдущей книгой [WorkbookA.xlsx].
3. Выберите все ячейки, которые вы хотите изменить, и нажмите CTRL+F и выберите вкладку замены.
4. Замените [WorkbookA.xlsx] пустым пространством (иначе ничего не пишите в поле « Replace with , нажмите « Replace All .

Вуаля - это сделано.

Answer 9

Выберите клетки, которые вы хотите переместить. Теперь попробуйте переместить их, перетащив n drop на другой лист (на другую вкладку).

Я знаю, это прокручивается. Вот сложная часть: просто нажмите cmd (mac) или alt (win), и он позволит вам бросить ячейки на другую вкладку.

Answer 10

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

Например, если первая формула =J2 и она становится =[filepath]J2 , то просто найдите и замените всю новую электронную таблицу для [filepath] и ничего не заменяйте. Это удалит его и восстановит формулу до =J2 .

VB не требуется!

Answer 11

1. Скопируйте лист как обычно. (щелкните правой кнопкой мыши вкладку и перейдите к пункту «Переместить или скопировать»). Это для форматирования.

2. Скопируйте все ячейки с исходного листа (используя Ctrl+A или верхний левый треугольник и Ctrl+C)

3. Вставить как значения в новую книгу (поверх листа «шаг-1») (Параметры вставки> 123)

Answer 12

Еще одна хитрость - перед копированием исходного листа замените все квалификаторы формулы = другим набором символов (скажем, ###=).

Скопируйте лист, затем после копирования замените спецификатор формулы обратно (заменив ###= на =).

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

Answer 13

Если вам нужно сделать это автоматически, потому что вы загружаете листы в программе VBA. Использовать этот:

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

Просто измените «Строка найдена в названии книги источника». соответствовать старым ссылкам, которые вы хотите заменить. Вы можете удалить это, если заблокировать, если вы хотите заменить все ссылки.

Answer 14

У меня была похожая проблема. Причина, по которой формулы были вставлены со ссылкой на WBA, заключалась в том, что вкладка (лист), над которой я работал в WBA, была названа иначе, чем в WBB. Для меня это всегда был "последний лист", но один назывался "MinFlow", а другой - "NormalFlow". Я переименовал оба в "Результаты", и копирование / вставка работали так, как я хотел - "относительная вставка".

Answer 15

Обе книги должны быть открыты, чтобы это работало. Вы запускаете этот макрос, и он будет копировать workbookA!sheet 1 к workbookB!sheet1 а затем заменить все ссылки на workbookA . Это грубо, но это работает. Очевидно, что вы можете изменить код в соответствии с именами WorkbookA.xlsx, но убедитесь, что они имеют правильное расширение и остаются в кавычках.

О, чтобы сделать макрос, если вы не знаете, нажмите Alt + F11, чтобы вызвать редактор Visual Basic. Затем щелкните правой кнопкой мыши на WBA- insert - module и скопируйте приведенный ниже код в модуль. Затем нажмите F5, чтобы запустить макрос. Если макрос не запускается, возможно, это связано с тем, что макросы не включены, поэтому сохраните его и снова откройте, а когда он предложит включить макросы, включите их.

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

Answer 16

Откройте обе рабочие книги.  В исходной рабочей книге (WorkbookA.xlsx) выберите лист, который вы хотите скопировать.  Щелкните правой кнопкой мыши вкладку листа и выберите «Переместить или скопировать…».  В диалоговом окне «Переместить или скопировать» выберите «WorkbookB.xlsx» в раскрывающемся списке «В книгу», выберите место в этой книге, куда вы хотите поместить его, и отметьте «Создать копию».  (И нажмите «ОК».)

Answer 17

Сделайте копию листа, который вы хотите переместить листы, из которого в этом случае будет WorkbookA.xlsx. Переименуйте его, чтобы сказать «Копия WorkbookA.xlsx». Теперь откройте эту новую рабочую книгу, а также рабочую книгу, которую вы хотите переместить, в которую в этом случае будет WorkbookB.xlsx. Щелкните правой кнопкой мыши листы в созданной вами копии книги, т.е. копии WorkbookA.xlsx, выберите «переместить или скопировать», а затем переместите эти листы в WorkbookB.xlsx. Вы сделали !