Записывайте и применяйте один макрос к множеству книг Excel без программирования

Довольно часто приходится работать с типовыми Excel файлами: регулярные отчеты, счета, заказы из региональных офисов, т.д. В итоге накапливается 200, 300 или даже 1000 однотипных документов с одинаковой структурой. А что если вам нужно изменить значение в конкретной ячейке, или удалить строку, или заменить какой-то текст во всех рабочих книгах? Как применить одинаковые изменения к каждой книге автоматически?

Инструмент XLTools «Зациклить макрос» помогает внести изменения во множество книг сразу:

«Зациклить макрос» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Скачать XLTools для Excel

Чтобы работать с инструментом «Зациклить макрос», нужно включить доступ к макросам в ваших настройках Excel:

Вкладка «Разработчик» Безопасность макросов Отметьте «Включить все макросы» и «Предоставлять доступ к объектной модели проектов VBA» ОК Перезапустите Excel.

Или:Файл Параметры Центр управления безопасностью Параметры центра управления безопасностью Параметры макросов Отметьте «Включить все макросы» и «Предоставлять доступ к объектной модели проектов VBA» ОК Перезапустите Excel.

Инструмент «Зациклить макрос» помогает зациклить макрос на множество книг Excel, другими словами, помогает применить один и и тот же макрос к большому числу рабочих книг.

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

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

Откройте вкладку XLTools В группе Автоматизация нажмите Зациклить макрос.



В окне «Зациклить макрос» выберите макрос, который нужно выполнить:

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



Ограничение:макросы из Личной книги макроса (Personal Macro Workbook) не доступны.
• Выберите только что записанный макрос – посмотрите, как записать новый макрос.




Чтобы выбрать файлы, нажмите иконку Найти Выберите папку с файлами, на которые нужно зациклить макрос Эти файлы будут добавлены в список При необходимости:
• Отметьте флажком Включить файлы в подпапках, и файлы будут добавлены
• Снимите флажки с тех файлов, которые нужно исключить из обработки


Нажмите Выполнить Подождите, пока макрос выполняется для каждой выбранной рабочей книги.

Внимание:Мы старалась сделать так, чтобы обработка шла как можно эффективнее. Она идет фоновым процессом, без открытия книг. Время обработки зависит от числа файлов для обработки, размера файлов, объема изменений и мощностей вашего компьютера.

Посмотрите результат для каждого файла, он приведен в столбце «Статус»:

• «готово» – макрос успешно выполнен
• «не обработан» – макрос не применялся к файлу
• «ошибка» – макрос отработал с ошибкой




Чтобы открыть файл, выберите его Нажмите правой кнопкой мыши Выберите Открыть этот файл или Открыть папку этой книги.

Откройте вкладку XLTools В группе Автоматизация нажмите Зациклить макрос Откроется окно Нажмите иконку Записать новый макрос.



В окне «Запись макроса» задайте имя макроса:

• Напишите понятное название макроса
• Опция «Сохранить в этой книге» будет задана по умолчанию
• Добавление сочетания клавиш и описание опционально




1. Нажмите ОК Начнется запись макроса Вносите необходимые изменения, которые нужно записать.
Когда будете готовы, нажмите кнопку Остановить запись панели инструментов XLTools.

Или:используйте комбинацию горячих клавиш Shift + Ctrl + M.



После остановки записи снова откроется окно «Зациклить макрос». Продвинутые пользователи могут изменить только что записанный макрос в редакторе Visual Basic.

Инструмент «Зациклить макрос» позволяет внести одинаковые изменения во все рабочие книги в папке одновременно.

Предположим, у вас есть папка с 200 рабочими книгами. Каждая книга содержит сводку ежемесячных заказов из одного регионального офиса, например «Северная Америка_январь». Все книги имеют одинаковую структуру. Вам нужно автоматически добавить расчеты по общей сумме в каждую книгу.

Совет:если вы планируете внести большой список изменений в книги, рекомендуем разбить процесс на несколько шагов и записать несколько мелких макросов вместо одного большого.

1. Прежде чем продолжить, сохраните резервные копии файлов.

2. Откройте одну из книг, которая послужит шаблонов, напр. «Северная Америка_январь».

3. Откройте вкладку XLTools В группе Автоматизация нажмите Зациклить макрос Откроется окно.
Нажмите иконку Записать новый макрос Задайте понятное имя макроса, напр., «ДобавитьОбщуюСумму» Нажмите ОК Теперь идет запись макроса, поэтому внесите необходимые изменения:
• Добавьте текст в ячейку E1: “ИТОГО:”
• Добавьте формулу в ячейку E2: =СУММ(C:C)
• Примените к столбцу С условное форматирование зеленую гистограмму. Это визуализирует относительный объем заказа.


4. Нажмите кнопку Остановить запись на вкладке XLTools Снова откроется окно «Зациклить макрос».
Выберите папку с файлами, в которые нужно внести такие же изменения Все файлы будут добавлены в список.


Нажмите Выполнить Подождите несколько минут, пока не закончится обработка Готово, все рабочие книги успешно обработаны.



Откройте одну из обработанных книг, напр. «Европа_апрель», и убедитесь, что все нужные изменения внесены верно.