Excel при добавлении строки копировались формулы

Обновлено: 01.07.2024

В Excel в защищенном листе отдельные интервалы ячеек (в столбцах) остаются доступными для ввода данных.
Соответственно в столбцах слева и справа от этого интервала заданы формулы, которые защищены от изменений.
При добавлении строк в защищенном листе нужно, чтобы копировались формулы вышерасположенной строки с сохранением их защиты.
Бьюсь с этим долго, никак не получается.

KL
[MVP - Microsoft Excel]

Изначальный код от vbapro (только вставка строк) запускается, а вставлять-удалять, -нет.

2. В редакторе ВБА добавить в своей книге модуль (в списке где перечислены наименования книг и листов на нужной книге кликнуть правой кнопкой и выбрать пункт меню inset -> module) и в него сохранить коды которые находятся в module1 файла KL(XL).

3. Создать (или скопировать из файла KL(XL)) кнопки "Вставить строки", "Удалить строки".

4. Правой кнопкой мыши назначить макрос каждой кнопке

5. Защитить лист и пробовать.

Can't execute code in break mode

You enter break mode when you suspend execution of code. This error has the following causes and solutions:

You tried to run code from the Macro dialog box. However, Visual Basic was already running code, although the code was suspended in break mode.
You may have entered break mode without knowing it, for example, if a syntax error or run-time error occurred. Continue running the suspended code, or terminate its execution before you run code from the Macro dialog box. You can fix the error and choose Continue, or you can return to the Macro dialog box and restart the macro.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

KL
[MVP - Microsoft Excel]

У меня появилась необходимость добавлять и удалять строки внутри двух списков на одном листе.
Пока я сделал по 2 макроса удаления и добавления на каждый список. разница в макросах только в номере списка: ActiveSheet.ListObjects( 1 ).Range

KL
[MVP - Microsoft Excel]

Как сделать чтобы одним макросом добавлялись и удалялись строки во всех списках листа?

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

В таблицу часто вносятся данные путём добавления строк (зачастую в хаотичном порядке то в середину то выше то ниже, но главное что не в первую и не в последнюю строку)
А на против этих данных есть формулы.
Так вот, можно ли как то при добавлении новой строки заставить Exel автоматически скопировать формулы с верхней строки.

Простой пример прилагаю.

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

Автоматическое создание файла .php при добавлении новой строки в таблицу БД PhpMyAdmin
Привет! Мне нужно реализовать автоматическое создание файла при добавлении строки в БД(В строке.

Автоматическое перемещение ползунка ListBox вниз при добавлении
Как сделать так чтобы при добавлении нового элемента в ListBox ползунок всегда находился внизу. Я.

Автоматическое заполнение одной таблицы при добавлении записи в другую
Здравствуйте! Подскажите,возможно ли в mssql 206 реализовать такую штучку,при добавление записей в.

Автоматическое копирование новой строки на другой лист
Башку сломал, ищу решение с помощью коллективного разума. Каждый раз при вводе данных через.

Решение

Отформатируйте таблицу как Таблицу и тогда все Ваши пожелания реализуются. А как это сделать? ))))
Обвожу ячейки нажимаю вторую кнопку мыши формат ячеек. А там нет таблицы (((
Я так понимаю я что то не так делаю. Kamekadze_rus, выделить таблицу - Главная - группа Стили - Форматировать как таблицу.
В столбцах должно быть названия и не объединяйте ячейки Всем большое спасибо!
Теперь разобрался где найти формат таблицы.
И про формулу МИН тоже спасибо за подсказку! учту при составлении таблицы.

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

QTextBrowser - автоматическое копирование текста при нажатии
Здравствуйте, есть определённый текст в QTextBrowser, мне нужно выделить одну из частей текста и.

Как сделать автоматическое копирование данных при их изменении?
Я не люблю C++, но вынужден программировать на нем. До этого я пользовался языком, в котором нет.

Excel. Копирование столбца, при заполнении 22-й строки продолжить копирование в соседний столбец
Всем привет. Задача: Есть таблица Excel, заполнены 2 столбца, из них первый - порядковые номера.

У меня есть строка в которой есть формулы, использующие значения одной и той же строки. Следующий ряд пуст, только с другим цветом фона.

теперь, если я вставляю новую строку (щелкнув правой кнопкой мыши на пустой строке и "вставить"), я получаю новую строку без цвета фона (что я хочу), но строка также не содержит формул: как я могу получить Excel, чтобы быть умнее и копировать формулы из предыдущей строки, при создании новой строки?

еще один кусок информация: информация проверки данных (т. е. выпадающий список) копируется при вставке новой строки.

сделайте область с вашими данными и формулами таблицей:

затем добавление новой информации в следующей строке скопирует все формулы в этой таблице для новой строки. Проверка данных также будет применяться для новой строки, как и для всего столбца. Это действительно Excel быть умнее с вашими данными.

не требуется VBA.

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

вы можете использовать приложение.ScreenUpdating = False, чтобы убедиться, что пользователь не видит, что вы переключаете листы в своем макрос.

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

вам нужно вставить новую строку, а затем скопировать из исходной строки во вновь вставленную строку. Excel позволяет вставить только формулы. Так в Excel:

вставить новую строку
скопировать исходную строку
выберите вновь созданную строку, щелкните правой кнопкой мыши и вставить
вставить как Формулы

VBA, если необходимые строки("1:1"), являющееся источником и строк("2:2") целевой:

Если у вас есть рабочий лист со многими строками, которые все содержат формулу, на сегодняшний день самый простой способ-скопировать строку без данных (но она содержит формулы), а затем "вставить скопированные ячейки" ниже/над строкой, где вы хотите добавить. Формулы остаются. В крайнем случае, можно использовать строку с данными. Просто очистите его или перепишите после вставки.

Как мы знаем, при копировании и вставке диапазона с формулой и форматированием в Excel формулы будут изменены, но форматирование останется обычным (см. Второй снимок экрана ниже). Некоторые пользователи могут попробовать Специальная вставка > Форматирование значений и источника, таким образом сохраняются результаты форматирования и формулы, но формулы теряются (см. третий снимок экрана ниже).

Есть ли способ сохранить и формулу, и форматирование при копировании и вставке в Excel? Здесь я расскажу о нескольких методах ее решения.

Сохранить диапазон как мини-шаблон (запись автотекста, остальные форматы ячеек и формулы) для повторного использования в будущем

Обычно Microsoft Excel сохраняет всю книгу как персональный шаблон. Но иногда вам может просто потребоваться часто повторно использовать определенный выбор. По сравнению с сохранением всей книги в виде шаблона, Kutools for Excel предоставляет симпатичный обходной путь Авто Текст Утилита для сохранения выбранного диапазона как записи автотекста, в которой могут оставаться форматы ячеек и формулы в диапазоне. И тогда вы сможете повторно использовать этот диапазон одним щелчком мыши. Полнофункциональная бесплатная 30-дневная пробная версия!

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Сохраните формулу и форматирование при копировании, изменив ссылку на формулу

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

1. Выберите диапазон, который вы скопируете с сохранением формулы и форматирования.

2. Нажмите Kutools > Скрытые ссылки; и в открывшемся диалоговом окне «Преобразование ссылок на формулы» установите флажок К абсолютному и нажмите Ok или Применить кнопка. Смотрите левый снимок экрана:

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

Внимание: Если у вас нет Kutools for Excel установлен, вы можете дважды щелкнуть ячейку с формулой в указанном диапазоне, выделить формулу (см. снимок экрана ниже) и нажать F4 ключ. И измените каждую ссылку формулы на абсолютную одну за другой.

Теперь каждая формула в указанном диапазоне изменяется на абсолютную, как показано ниже:

Шаг 3: Теперь выберите указанный диапазон, скопируйте, нажав Ctrl + C одновременно, выберите пустую ячейку и вставьте, нажав Ctrl + V ключи одновременно.

Теперь указанный диапазон копируется и вставляется с оставшейся его формулой и форматированием, как показано ниже:

Примечание: При необходимости вы можете изменить вставленные формулы и исходные формулы на относительные ссылки, выделив их и нажав Kutools > Преобразовать ссылки > К относительной > OK.

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

Если у вас установлен Kutools for Excel, его Точная копия Утилита может помочь вам сохранить формулы и форматирование при копировании и вставке в Excel.

Шаг 1: Выберите диапазон, который вы скопируете с формулой и форматированием.

Шаг 2: нажмите Kutools > Точная копия, и в появившемся диалоговом окне Exact Formula Copy отметьте Copy форматирование и нажмите Ok кнопка. См. Снимок экрана ниже:

Шаг 3. Теперь откроется второе диалоговое окно «Копирование точной формулы», выберите пустую ячейку и щелкните значок OK кнопку.

Теперь вы увидите, что выбранный диапазон скопирован и вставлен с оставшейся точной формулой и форматированием, как показано ниже:

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Читайте также: