Записывайте и применяйте один макрос к множеству книг 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. Откройте одну из рабочих книг, в которые вы хотите внести изменения. Она послужит шаблоном.

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

   

   

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

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

     

     Ограничение: макросы из Личной книги макроса (Personal Macro Workbook) не доступны.

   • Выберите только что записанный макрос – посмотрите, как записать новый макрос.

   

   

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

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

   

   

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

   

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

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

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

   
   

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

   

   

Инструмент «Зациклить макрос» интегрирован с нативным Средством записи макросов в Excel. Вы можете записать макрос на лету и потом зациклить его на множество книг.

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

   

   

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

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

   

   

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

4. Когда будете готовы, нажмите кнопку Остановить запись панели инструментов XLTools.

   

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

   

   

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

   

   

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

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

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

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

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

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

4. Нажмите иконку Записать новый макрос Задайте понятное имя макроса, напр., «ДобавитьОбщуюСумму» Нажмите ОК Теперь идет запись макроса, поэтому внесите необходимые изменения:

   • Добавьте текст в ячейку E1: “ИТОГО:”
   • Добавьте формулу в ячейку E2: =СУММ(C:C)
   • Примените к столбцу С условное форматирование зеленую гистограмму. Это визуализирует относительный объем заказа.

   

   

5. Нажмите кнопку Остановить запись на вкладке XLTools Снова откроется окно «Зациклить макрос».

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

   

   

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

   

   

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