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

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

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

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

1. создание и сохранение целевой таблицы.

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

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

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

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

Нажмите кнопку Закрыть и вы закончите.

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

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

=database_feed!A1

для этого:

=INDIRECT("database_feed!A1")

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

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

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

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

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

• копировать лист в ' WorkbookB.xlsx'
• открыть таблицу в новом файле
• выделить все
• зайдите в меню Data, нажмите на edit links
• изменить ссылки так, что ссылка на старый файл, теперь ссылка на в настоящее время открыть файл

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

обе книги должны быть открыты для этой работы. Запустите этот макрос, и он скопирует workbookA!sheet1 to 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

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

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

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

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

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

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

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

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

вуаля - это сделанный.

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

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

убедитесь, что все ссылки на листы в formauls также копируются на новый лист до ссылающегося листа.

с 99% ответов не по адресу Вопрос, вот правильный ответ.

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

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

3. в новом файле в разделе Данные нажмите кнопку " Изменить Ссылки"

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

5. найдите файл (новый.xlsx) и нажмите кнопку Открыть.

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

готово!

1. скопировать лист как обычно. (щелкните правой кнопкой мыши на вкладке и перейдите к "переместить или скопировать") это для форматирования.

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

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

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

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

откройте обе книги.  В исходной книге (WorkbookA.xlsx), выберите лист, который требуется скопировать.  Щелкните правой кнопкой мыши на вкладке листа и выберите " переместить или скопировать...".  В диалоговом окне " перемещение или копирование "выберите" WorkbookB.xlsx "в раскрывающемся списке" забронировать, выберите, где в этой книге вы хотите поместить его, и установите флажок "Создать копию".  (И нажмите кнопку "OK".)