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

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

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

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

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

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

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

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

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

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

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

Щелкните “Закрыть”, и все готово.

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

Преобразовать это:

=database_feed!A1

в это:

=INDIRECT("database_feed!A1")

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

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

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

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

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

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

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

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

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

3. В новом файле, в разделе Data, нажмите “Edit Links”

4. Во всплывающем окне выберите “Change Source…”

5. Найдите файл (New.xlsx) и нажмите Open.

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

DONE!

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

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

Если вам нужно сделать это автоматически, потому что вы втягиваете листы в программу 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

Просто измените “String found in source book name.”, чтобы соответствовать старым ссылкам, которые вы хотите заменить. Вы можете удалить его, если хотите заменить все ссылки.

Еще один “трюк” - перед копированием рабочего листа-источника замените все определители формул = на другой набор символов (скажем, ###=).

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

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

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

Oh, чтобы сделать макрос, в случае, если вы не знаете, нажмите alt + F11, чтобы открыть редактор Visual Basic Editor. Затем щелкните правой кнопкой мыши на 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
```.

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

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

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

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

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

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

H

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

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

3. Вставить как Значения в новую рабочую книгу (на листе “Шаг 1”) (Вставить Options>123).

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

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