Excel: Как скопировать лист с формулами из одной книги в другую

Очередная тривиальная задача сегодня просто выбесила, а именно в грёбанном экселе понадобилось скопировать лист с формулами из одной книги в другую. Причём формул там over9000 — генерация Актов по приёмкам узлов учёта.

Всё началось с того что файлик экселя распух до 35 метров и я решил бодренько пересохранить его.

Однако при замене основного листа на листе актов тут же херились все ссылки, а при копировании этого же листа из другой книги — грамотный офис делал перелинковку на старый файл. И это fail, от которого не отмазаться (ну, у меня в 2010м офисе не получилось штатными опциями отказаться от связи с другим документом).

В итоге нагуглилось несколько вариантов:

1й — использовать функцию ДВССЫЛ(), и еще иногда функцию ИНДЕКС. ДВССЫЛ() должна заменить все ссылки в формулах на листе СВОД, тогда при копировании в др. книгу формулы будут тянуть данные из листов новой книги.

Проверять не стал, надо вариант проще.

2й — использовать макросы

Sub ATS()
Const WS_NAME = «Лист1»
Const DST_NAME = «Книга2»
Dim c As Range

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With Worksheets(WS_NAME)
.Copy Before:=Workbooks(DST_NAME).Sheets(1)
For Each c In .UsedRange.SpecialCells(xlCellTypeFormulas).Cells
Cells(c.Row, c.Column).Formula = c.Formula
Next
End With
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Или вот:

Sub ATS()

Const WS_NAME = «СВОД»
Dim c As Range
Dim filename As String

iOpen = Application.Dialogs(xlDialogOpen).Show
filename = ActiveWindow.Caption
If iOpen <> True Then
MsgBox «Вы не выбрали книгу», , «»
End If

Windows(«Экономическая часть по т.э.на 2009г со сводом.xls»).Activate

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With Worksheets(WS_NAME)
.Copy Before:=Workbooks(filename).Sheets(1)
For Each c In .UsedRange.SpecialCells(xlCellTypeFormulas).Cells
Cells(c.Row, c.Column).Formula = c.Formula
Next
End With
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Вариант хорош, но как бы не наше это всё, макросов у меня в книге и так хватает.

3й вариант:

при копировании формул из книги в книгу (Правка — Специальная вставка — Формулы) обнаружил способ избавиться от связи с книгой-оригиналом.
Правка — Связи… — кнопка Изменить, и привязываем к самой книге-копии. Связи исчезают.
В Excel 2007: данные — в подключениях «изменить связи» — изменить — и выбираете новую книгу.

У себя не нашел «куда нажать» чтобы призвать эту неведомую функцию.

4й вариант: Мой любимый. Я сразу понял, что это моё!

Выделяем всё, жмем автозамену ctrl+h, заменяем «=» на «БЛАБЛАБЛА», нажимаем копировать лист в другую книгу, еще раз жмём автозамену и меняем обратно «БЛАБЛАБЛА» на «=».
Всё!

Вдруг кому срочно пригодится скопировать пару формул. Пару тысяч формул.

Рейтинг: 0

Комменты из Vk:

Оставите комментарий ?

14 Комментарии。

  1. Значит не пригодились макросы :-)

    Рейтинг: 0
  2. Всё гениальное просто :-D
    Хотя, честно говоря, я бы воспользовался третьим вариантом.

    Рейтинг: 0
  3. Забавно…
    Не, я просто не пользуюсь таким говном как Офис моложе 2003-го (ровно потому, что в 2007 и выше просто невозможно разобраться в структуре меню, а help писали какие-то мудаки. Впрочем, те же самые мудаки писали его и для 2003-го, но там хоть меню понятное), видимо, поэтому подобной проблемы никогда не возникало. А может быть, просто до объема такого не доходил, хотя 9000 формул — это, прямо скажем, мало. Не в смысле, что легко восстанавливается, а просто — мало. У меня в процессе работы в файле финансовых сводок столько набегает за квартал, а файл ведется годами.
    Блин, заинтриговал. Попробую завтра копирнуть че-нть.

    Рейтинг: 0
  4. Mefistofrel777

    хз …4 вариант при аналогичной задаче пришёл мне в сознание сразу… ну и последующих телодвижений не требовалось))

    другие варианты пригодятся если есть уже имеющиеся макросы(в большом количестве) завязанные на конкретные ссылки.

    Рейтинг: 0
  5. Аноним

    palmface

    Рейтинг: 0
  6. Виктория

    Вы гений!!

    Рейтинг: 0
  7. Аноним

    *THUMBS UP*

    Рейтинг: 0
  8. Андреич

    3-й вариант отработал. Все работает.

    Рейтинг: 0
  9. Виталий

    4 вариант не работает, когда надо перенести формулу типа =ВПР(), вставленную cntr+shift+enter, т.к. она сбивается.

    Рейтинг: 0
  10. Филипп

    4 вариант рулезззз!!! Спасибо!

    Рейтинг: 0
  11. adolfych@mail.ru

    Четвертый!!!!!!! Гениально!

    Рейтинг: 0
  12. Аноним

    Ооо, это очень… красиво (четвертый вариант).

    Рейтинг: 0

Оставить комментарий

Примечание - Вы можете использовать эти HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <img http="" alt="" height="" src="" width=""> <iframe alt="" height="" src="" width=""> <ul> <li> <ol> <src> <p>

Яндекс.Метрика

Copyleft 2010 - 2016 © Obobrali.ru
Disclaimer
Все права на оригинальные тексты и картинки принадлежат их авторам
Все материалы на сайте рассчитаны на категорию адекватных людей 18+




Авторизация

Регистрация

captcha image

Генерация пароля