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

Другая проблема с копией в Excel:

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

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

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

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

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

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

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

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

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

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

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

  3. Затем перейдите в меню «Правка» и нажмите «Ссылки». Опция не будет активна, если на странице не будет ссылок.

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

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

Нажмите «Закрыть», и вы закончите.

Или просто выполните следующие действия:

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

 =database_feed!A1 

к этому:

 =INDIRECT("database_feed!A1") 

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

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

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

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

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

 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»
  • Открыть рабочий лист в новом файле
  • выбрать все
  • Перейдите в меню «Данные», нажмите ссылку редактирования
  • Редактировать ссылки, чтобы ссылка на старый файл теперь была ссылкой на открытый файл

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

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

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

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

Нет необходимости в VB!

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

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

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

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

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

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

Другой «трюк» – перед копированием исходного листа замените все квалификаторы формулы = на другой набор символов (скажем, ###= ).

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

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

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

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

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

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

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

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

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

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

  3. В новом файле в разделе «Данные» нажмите «Изменить ссылки»,

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

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

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

СДЕЛАННЫЙ!

  • Преобразование строкового представления константы в константу?
  • VBA Runtime Error 1004 «Определенная пользователем или объектная ошибка» при выборе диапазона
  • Перегрузка функций и UDF в Excel VBA
  • Возrotation регулярного выражения в VBA (excel)
  • Полностью указать рабочий лист по кодовому имени
  • 800A0401 - Ожидаемый конец заявления
  • Как узнать, содержит ли массив строку
  • Найти похожие сообщения в Outlook 2010 с новым / всплывающим окном, как в 2007 году
  • Кнопка Microsoft Powerpivot не работает
  • Макрос VBA падает после 32000 строк
  • excel macro (VBA) для переноса нескольких столбцов в несколько строк
  • Interesting Posts

    Cloning Fresh Windows 7 -fsed HDD для Linux Server, потому что не имеет внешнего жесткого диска или дисков для резервного копирования

    Развертывание Java с помощью SCCM / SCUP

    Несколько параметров для селектора jQuery?

    Как я могу автоматизировать диалоговое окно «Сохранить как» в IE11 с помощью VBA?

    Запуск команд оболочки, хотя java-код на Android?

    Свободное выражение и выражение запроса. Есть ли какая-либо польза от других?

    Измените размер большого файла растрового изображения на масштабируемый выходной файл на Android

    Как использовать функцию srand () с time.h?

    Случайно создал вирус?

    Объединение двух изображений

    Что означает этот непечатаемый символ в Microsoft Word?

    Доступность: рекомендуемое соглашение об альт-тексте для SVG и MathML?

    Как вы запрашиваете статические .html-файлы в папке ~ / Views в ASP.NET MVC?

    Excel. Как vlookup вернуть несколько значений?

    Загрузка в Windows с USB-накопителя, созданного на OSX

    Давайте будем гением компьютера.