Если данные лежат на разных листах Excel, их не нужно копировать руками. Показываю макрос VBA, который собирает один общий отчёт из 10 листов, пропускает заголовки, находит последнюю строку и защищает от типичных ошибок новичка.
Макрос собирает отчёт из 10 листов Excel за 3 секунды — код и полный разбор
Есть особый вид офисной боли: когда данные лежат не в одной таблице, а размазаны по десяти листам. На одном листе январь, на другом февраль, на третьем отдел продаж, на четвёртом склад, на пятом филиал, на шестом кто-то назвал вкладку «новый отчёт финал 2». И всё это нужно собрать в один общий отчёт.
Руками это выглядит знакомо: открыть первый лист, выделить данные, скопировать, перейти на итоговый лист, вставить. Потом второй. Потом третий. На шестом листе случайно захватить заголовок. На восьмом вставить не туда. На десятом обнаружить, что в одном листе пустая строка посередине, и Excel решил, что жизнь слишком коротка для порядка.
Хорошая новость: такую работу отлично забирает макрос. Не потому что VBA — магия, а потому что задача повторяемая. Excel должен пройти по листам, взять данные, пропустить шапку, найти последнюю строку и сложить всё в один лист. Это скучная работа. А скучную работу машине и надо отдавать.
В этой статье соберём рабочий макрос, который создаёт общий отчёт из нескольких листов. Разберём код по частям, покажем, где новички ошибаются, и сделаем так, чтобы макрос не разваливался при первом пустом листе.
Какая задача у нас есть
Представим обычную книгу Excel. В ней несколько листов с одинаковой структурой:
Дата
Товар
Категория
Количество
Цена
Сумма
На каждом листе первая строка — заголовки. Данные начинаются со второй строки. Нам нужно собрать все строки со всех рабочих листов в один лист Сводный_отчёт.
Вручную это можно сделать. Но если листов 10, 20 или 30, ручной способ становится ловушкой. Один раз ошиблись с диапазоном — отчёт уже врёт. А самое неприятное, что ошибку можно не заметить.
Макрос должен сделать 5 вещей:
1. Создать или очистить итоговый лист.
Чтобы каждый запуск давал свежий результат.
2. Пройти по всем листам книги.
Кроме самого итогового листа.
3. Найти последнюю заполненную строку на каждом листе.
Потому что количество строк везде разное.
4. Скопировать данные без заголовков.
Заголовки нужны только один раз — в итоговом отчёте.
5. Вставить данные друг под другом.
Без пустых разрывов и случайного наложения строк.
Вот это уже нормальная автоматизация. Не «нажмите три кнопки», а реальное снятие рутины.
Подготовьте файл перед запуском
Перед макросом наведите минимальный порядок. Не идеальный, не музейный. Просто такой, чтобы Excel понимал, где что лежит.
На каждом исходном листе должны быть одинаковые столбцы:
A — Дата
B — Товар
C — Категория
D — Количество
E — Цена
F — Сумма
Первая строка — заголовки. Данные начинаются со второй строки. Если на одном листе «Дата» стоит в A, а на другом в C, макрос соберёт не отчёт, а винегрет с формулами. Excel не обязан угадывать авторский замысел. Он честный исполнитель: что дали, то и сложил.
Сохраните файл в формате .xlsm. Обычный .xlsx макросы не сохранит. Для этого откройте «Файл → Сохранить как», в типе файла выберите «Книга Excel с поддержкой макросов (.xlsm)» и сохраните.
И обязательно сделайте копию файла перед первым запуском. Макрос будет создавать и очищать итоговый лист. Это нормально, но первые тесты лучше делать не на боевом отчёте.
Полный макрос для сборки отчёта
Ниже готовый рабочий макрос. Он проходит по всем листам книги, берёт данные из столбцов A:F, пропускает заголовки и собирает всё на лист Сводный_отчёт.
Sub CollectReportFromSheets()
Dim ws As Worksheet
Dim reportWs As Worksheet
Dim lastRow As Long
Dim nextRow As Long
Dim sourceRange As Range
Dim reportName As String
reportName = "Сводный_отчёт"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Set reportWs = ThisWorkbook.Worksheets(reportName)
On Error GoTo 0
If reportWs Is Nothing Then
Set reportWs = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
reportWs.Name = reportName
Else
reportWs.Cells.Clear
End If
Application.DisplayAlerts = True
reportWs.Range("A1:F1").Value = Array("Дата", "Товар", "Категория", "Количество", "Цена", "Сумма")
reportWs.Rows(1).Font.Bold = True
nextRow = 2
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> reportName Then
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow >= 2 Then
Set sourceRange = ws.Range("A2:F" & lastRow)
sourceRange.Copy Destination:=reportWs.Range("A" & nextRow)
nextRow = reportWs.Cells(reportWs.Rows.Count, "A").End(xlUp).Row + 1
End If
End If
Next ws
reportWs.Columns("A:F").AutoFit
reportWs.Range("A1:F1").AutoFilter
Application.ScreenUpdating = True
MsgBox "Сводный отчёт собран. Строк добавлено: " & nextRow - 2, vbInformation
End Sub
Этот макрос уже можно запускать. Но просто вставить код — мало. Нужно понимать, что внутри, иначе при первой нестандартной таблице начнётся старая песня: «оно само сломалось». В Excel обычно ничего само не ломается. Ему помогают.
Куда вставить макрос
Откройте Excel-файл, который сохранён как .xlsm, и зайдите в редактор VBA:
Нажмите Alt + F11
В меню выберите Вставка → Модуль
Вставьте макрос в открывшееся окно
Закройте редактор VBA
Вернитесь в Excel
Нажмите Alt + F8
Выберите CollectReportFromSheets
Нажмите Выполнить
После запуска в книге появится лист Сводный_отчёт. Если он уже был, макрос очистит его и соберёт данные заново.
Это удобно: можно запускать макрос каждый раз после обновления исходных листов. Старый результат не будет смешиваться с новым. Сводный отчёт всегда пересобирается с чистого листа.
Что делает первая часть макроса
Сначала мы объявляем переменные. Это рабочие «коробки», куда макрос будет складывать листы, номера строк, диапазоны и имя итогового листа.
Dim ws As Worksheet
Dim reportWs As Worksheet
Dim lastRow As Long
Dim nextRow As Long
Dim sourceRange As Range
Dim reportName As String
Здесь всё довольно просто. ws — текущий лист, по которому макрос идёт в цикле. reportWs — итоговый лист. lastRow — последняя заполненная строка на исходном листе. nextRow — следующая свободная строка в итоговом отчёте. sourceRange — диапазон, который копируем. reportName — имя итогового листа.
Дальше задаём имя отчётного листа:
reportName = "Сводный_отчёт"
Можно назвать иначе: Итог, Общий_отчёт, Report, Сборка. Но лучше без пробелов и странных символов. Excel, конечно, терпеливый, но не надо испытывать его характер.
Почему мы отключаем обновление экрана
В макросе есть строка:
Application.ScreenUpdating = False
Она отключает постоянное визуальное обновление экрана. Excel не будет показывать каждое копирование и каждое переключение. Макрос отработает быстрее и спокойнее. В конце мы включаем обновление обратно:
Application.ScreenUpdating = True
Если этого не сделать, Excel может вести себя странно: экран не обновляется, пользователь нервничает, рабочий день темнеет. Поэтому важное правило: отключили что-то в начале — включили обратно в конце.
Есть ещё строка Application.DisplayAlerts = False. Она временно отключает предупреждения Excel. В нашем макросе это нужно аккуратно, на этапе работы с листом. Но здесь мы не удаляем лист, а только очищаем, поэтому можно было бы обойтись и без неё. Я оставил эту строку как пример, но включил предупреждения обратно сразу после подготовки итогового листа строкой Application.DisplayAlerts = True.
Новичкам важно не злоупотреблять отключением предупреждений. Если макрос удаляет листы, закрывает файлы или перезаписывает данные, отключённые предупреждения могут превратить маленькую ошибку в большой офисный спектакль.
Как макрос создаёт или очищает итоговый лист
Эта часть проверяет, есть ли в книге лист Сводный_отчёт:
On Error Resume Next
Set reportWs = ThisWorkbook.Worksheets(reportName)
On Error GoTo 0
Если лист найден, переменная reportWs получает ссылку на него. Если листа нет, ошибка временно игнорируется, а дальше макрос сам создаёт новый лист:
If reportWs Is Nothing Then
Set reportWs = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
reportWs.Name = reportName
Else
reportWs.Cells.Clear
End If
По-человечески: если листа Сводный_отчёт нет — создать его в начале книги; если лист уже есть — очистить все ячейки.
Зачем очищать? Чтобы при повторном запуске старые данные не остались внизу. Это частая ошибка: макрос вставляет новые строки под старые, и отчёт становится в два раза больше. Человек радуется росту продаж, а потом выясняется, что это не продажи выросли, а макрос дважды скопировал январь.
Как добавляются заголовки
После подготовки листа макрос создаёт шапку:
reportWs.Range("A1:F1").Value = Array("Дата", "Товар", "Категория", "Количество", "Цена", "Сумма")
reportWs.Rows(1).Font.Bold = True
Заголовки добавляются только один раз на итоговый лист. С исходных листов заголовки не копируются. Это важно. Если копировать всё с первой строки каждого листа, итоговый отчёт будет выглядеть так: заголовки, данные, заголовки, данные, заголовки, данные. А потом фильтр, сортировка и сводные таблицы начнут вести себя так, будто их пригласили на свадьбу к незнакомым людям.
Как макрос проходит по всем листам
Главная часть — цикл:
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> reportName Then
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow >= 2 Then
Set sourceRange = ws.Range("A2:F" & lastRow)
sourceRange.Copy Destination:=reportWs.Range("A" & nextRow)
nextRow = reportWs.Cells(reportWs.Rows.Count, "A").End(xlUp).Row + 1
End If
End If
Next ws
Макрос берёт каждый лист в книге. Но сразу проверяет условие If ws.Name <> reportName Then — это значит: не обрабатывать сам итоговый лист. Если этого не сделать, макрос может начать копировать сводный отчёт в сводный отчёт. Получится змея, которая ест свой хвост, только в Excel и с бухгалтерским оттенком.
Дальше макрос ищет последнюю заполненную строку в столбце A строкой lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. Это один из базовых приёмов VBA. Он идёт в самый низ столбца A и поднимается вверх до первой заполненной ячейки. Так мы узнаём, где заканчиваются данные.
Потом проверка If lastRow >= 2 Then: если последняя строка меньше 2, значит на листе нет данных ниже заголовка. Такой лист пропускаем. Это защита от пустых листов.
Почему данные копируются с A2, а не с A1
Диапазон задаётся так: Set sourceRange = ws.Range("A2:F" & lastRow). Мы начинаем с A2, потому что первая строка — заголовки. В итоговом отчёте заголовки уже есть. Нам нужны только данные.
Если ваши данные начинаются не со второй строки, а, например, с пятой, нужно заменить A2 на A5. Но лучше привести исходные листы к единому виду. Чем меньше исключений, тем надёжнее макрос. Вот рабочая истина Excel: хороший макрос начинается не с кода, а с нормальной структуры таблицы.
Как макрос понимает, куда вставлять следующую порцию данных
В начале мы задаём nextRow = 2. Это значит: первые данные вставляем со второй строки итогового листа, сразу под заголовками. После копирования макрос обновляет номер следующей свободной строки:
nextRow = reportWs.Cells(reportWs.Rows.Count, "A").End(xlUp).Row + 1
Он смотрит, где теперь заканчиваются данные в итоговом отчёте, и прибавляет 1. Следующая порция вставится ниже. Это надёжнее, чем вручную считать строки. Особенно если на разных листах разное количество данных. Сегодня на первом листе 300 строк, на втором 18, на третьем 950. Макрос не спорит. Он просто каждый раз ищет свободное место.
Как сделать макрос только для 10 конкретных листов
Иногда в книге есть лишние листы: справочник, настройки, инструкция, формулы, архив. Если макрос пойдёт по всем листам, он может захватить то, что не нужно. Тогда лучше обрабатывать только листы с конкретными именами — например, Лист1, Лист2, Лист3 и так до Лист10.
Вот вариант макроса для конкретного списка листов:
Sub CollectReportFromTenSheets()
Dim reportWs As Worksheet
Dim ws As Worksheet
Dim sheetNames As Variant
Dim sheetName As Variant
Dim lastRow As Long
Dim nextRow As Long
Dim reportName As String
reportName = "Сводный_отчёт"
sheetNames = Array("Лист1", "Лист2", "Лист3", "Лист4", "Лист5", "Лист6", "Лист7", "Лист8", "Лист9", "Лист10")
Application.ScreenUpdating = False
On Error Resume Next
Set reportWs = ThisWorkbook.Worksheets(reportName)
On Error GoTo 0
If reportWs Is Nothing Then
Set reportWs = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
reportWs.Name = reportName
Else
reportWs.Cells.Clear
End If
reportWs.Range("A1:F1").Value = Array("Дата", "Товар", "Категория", "Количество", "Цена", "Сумма")
reportWs.Rows(1).Font.Bold = True
nextRow = 2
For Each sheetName In sheetNames
On Error Resume Next
Set ws = ThisWorkbook.Worksheets(CStr(sheetName))
On Error GoTo 0
If Not ws Is Nothing Then
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow >= 2 Then
ws.Range("A2:F" & lastRow).Copy Destination:=reportWs.Range("A" & nextRow)
nextRow = reportWs.Cells(reportWs.Rows.Count, "A").End(xlUp).Row + 1
End If
End If
Set ws = Nothing
Next sheetName
reportWs.Columns("A:F").AutoFit
reportWs.Range("A1:F1").AutoFilter
Application.ScreenUpdating = True
MsgBox "Отчёт из 10 листов собран. Строк добавлено: " & nextRow - 2, vbInformation
End Sub
Этот вариант лучше, если в книге много служебных листов. Он не трогает всё подряд, а работает только с теми вкладками, которые вы перечислили.
Главное — имена листов в массиве должны совпадать с реальными именами вкладок. Если у вас лист называется Январь, а в коде написано Лист1, макрос его не найдёт. Excel не понимает «ну ты понял». Он понимает только точное имя.
Если хотите не переписывать всё руками, в Telegram я выложу готовую книгу-тренажёр: несколько листов с данными, правильная структура и все три варианта макроса внутри. Открываете, запускаете на копии, смотрите, как собирается отчёт — и адаптируете под свой файл.
Как добавить колонку с названием исходного листа
Иногда в итоговом отчёте нужно видеть, откуда пришла строка. Например, если каждый лист — это филиал, месяц, менеджер или отдел. Тогда полезно добавить отдельную колонку Источник.
Вот версия, которая собирает данные и добавляет имя исходного листа в столбец G:
Sub CollectReportWithSourceSheet()
Dim ws As Worksheet
Dim reportWs As Worksheet
Dim lastRow As Long
Dim nextRow As Long
Dim rowsCount As Long
Dim reportName As String
reportName = "Сводный_отчёт"
Application.ScreenUpdating = False
On Error Resume Next
Set reportWs = ThisWorkbook.Worksheets(reportName)
On Error GoTo 0
If reportWs Is Nothing Then
Set reportWs = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
reportWs.Name = reportName
Else
reportWs.Cells.Clear
End If
reportWs.Range("A1:G1").Value = Array("Дата", "Товар", "Категория", "Количество", "Цена", "Сумма", "Источник")
reportWs.Rows(1).Font.Bold = True
nextRow = 2
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> reportName Then
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow >= 2 Then
rowsCount = lastRow - 1
ws.Range("A2:F" & lastRow).Copy Destination:=reportWs.Range("A" & nextRow)
reportWs.Range("G" & nextRow & ":G" & nextRow + rowsCount - 1).Value = ws.Name
nextRow = nextRow + rowsCount
End If
End If
Next ws
reportWs.Columns("A:G").AutoFit
reportWs.Range("A1:G1").AutoFilter
Application.ScreenUpdating = True
MsgBox "Отчёт собран с указанием источника. Строк добавлено: " & nextRow - 2, vbInformation
End Sub
Этот вариант я чаще всего советую для реальной работы. Потому что через неделю вы можете открыть сводный отчёт и спросить: «А эта строка откуда взялась?» Колонка Источник сразу даёт ответ.
Для отчётов по месяцам это особенно удобно. Лист называется Март, и каждая строка в сводном отчёте получает источник Март. Потом можно построить сводную таблицу и быстро увидеть итоги по месяцам.
Типичные ошибки новичков
Первая ошибка — разные структуры листов. На одном листе столбцы A:F, на другом A:G, на третьем сумма стоит не в F, а в H. Макрос соберёт данные так, как написано в коде. Если структура разная, результат будет кривым. Решение: перед запуском привести листы к одной структуре — одинаковые заголовки, одинаковые столбцы, данные с одной строки.
Вторая ошибка — пустой столбец A. Макрос ищет последнюю строку по столбцу A. Если в столбце A есть пропуски или он вообще не главный, макрос может определить конец таблицы неправильно. Решение: искать последнюю строку по надёжному столбцу — например, по номеру заказа, дате или ID. Если дата в столбце B, замените "A" на "B" в строке поиска последней строки.
Третья ошибка — запуск макроса на несохранённом файле без копии. Особенно когда человек «просто проверит». Эти слова в Excel опаснее, чем кажется. «Просто проверю» обычно заканчивается поиском старой версии в загрузках. Решение: перед первым запуском сделать копию файла.
Четвёртая ошибка — случайно обработать служебные листы. Если в книге есть листы Настройки, Справочник, Инструкция, лучше использовать вариант с конкретным списком листов.
Пятая ошибка — забыть включить макросы. Если Excel открыл файл в защищённом режиме, код не запустится. Нужно разрешить содержимое только если вы доверяете файлу. Чужие файлы с макросами открывать бездумно нельзя.
Как назначить запуск на кнопку
Чтобы не открывать каждый раз список макросов через Alt + F8, можно сделать кнопку на листе:
Откройте вкладку «Разработчик»
Нажмите «Вставить»
Выберите «Кнопка» из элементов формы
Нарисуйте кнопку на листе
Выберите макрос CollectReportFromSheets
Переименуйте кнопку в «Собрать отчёт»
Теперь пользователь открывает файл и нажимает одну кнопку. Это уже выглядит не как «код для программиста», а как нормальный рабочий инструмент.
Лучше поставить кнопку на отдельный лист Панель или Старт. Там же можно написать короткую инструкцию: вставьте данные на исходные листы, нажмите «Собрать отчёт», проверьте лист «Сводный_отчёт». Чем проще интерфейс, тем меньше шансов, что человек полезет в код и устроит там ремонт без каски.
Как проверить результат
После первого запуска не верьте макросу на слово. Проверка нужна всегда.
Проверьте 5 вещей:
1. Сколько строк было на исходных листах.
Сложите количество строк без заголовков.
2. Сколько строк получилось в сводном отчёте.
Количество должно совпасть.
3. Не попали ли заголовки внутрь данных.
Если в середине отчёта встречается строка «Дата, Товар, Категория», значит копировали с первой строки.
4. Не попали ли служебные листы.
Если видите странные строки из инструкции или справочника, используйте вариант со списком листов.
5. Не съехали ли столбцы.
Дата должна быть датой, товар — товаром, сумма — суммой. Звучит очевидно, но именно на очевидном чаще всего и падают.
Макрос экономит время, но проверка результата остаётся за человеком. Автоматизация не отменяет голову. Она просто освобождает её от механической работы.
Когда этот макрос особенно полезен
Такой макрос подходит для десятков бытовых сценариев:
Сбор продаж по филиалам.
Каждый лист — отдельный филиал, итоговый отчёт собирает всё вместе.
Сбор данных по месяцам.
Январь, февраль, март, апрель — в один общий список.
Сбор заявок от менеджеров.
Каждый менеджер ведёт свой лист, макрос собирает общую базу.
Сбор складских остатков.
Разные склады или категории складываются в один отчёт.
Сбор расходов.
Каждый лист — отдельное направление затрат.
Сбор учебных или рабочих задач.
Каждый отдел заполняет свою вкладку, общий лист получает всё.
Главное условие одно: одинаковая структура таблиц. Если она есть, макрос работает уверенно. Если её нет, сначала лечим таблицу, потом пишем код.
Мини-чек-лист перед запуском
Перед тем как нажать кнопку, проверьте:
1. Файл сохранён как .xlsm.
Иначе макрос не сохранится.
2. Есть резервная копия.
Особенно перед первым запуском.
3. Исходные листы имеют одинаковые столбцы.
Дата, товар, категория, количество, цена, сумма.
4. Заголовки стоят в первой строке.
Данные начинаются со второй.
5. Столбец A заполнен в каждой строке данных.
Или код изменён под другой надёжный столбец.
6. Итоговый лист называется Сводный_отчёт.
Или имя в коде изменено.
7. Служебные листы не попадут в сборку.
Если они есть — используйте вариант с конкретным списком листов.
8. После запуска сверены строки.
Автоматизация любит проверку. Как старый мастер: доверяй, но линейку держи рядом.
Что дальше можно улучшить
Этот макрос — хорошая база. Но его можно развивать. Можно добавить проверку заголовков, чтобы макрос перед сборкой убеждался, что на каждом листе структура одинаковая. Можно добавить дату сборки отчёта в отдельную ячейку. Можно добавить очистку пустых строк или сортировку итогового отчёта по дате. Можно добавить создание сводной таблицы после сборки, сохранение итогового отчёта в отдельный файл или кнопку «Собрать и сохранить PDF».
Но не надо делать всё сразу. Сначала пусть макрос уверенно решает одну задачу: собирает данные из 10 листов в один отчёт. Когда эта основа работает, улучшения добавляются спокойно.
Главное
Если вы каждый день копируете данные из нескольких листов в один общий отчёт, вы делаете работу, которую Excel должен делать сам. Не потому что вы ленивы, а потому что человек нужен для проверки, анализа и решений. А не для бесконечного «копировать — вставить — поправить — ой, не туда».
Макрос из этой статьи снимает одну из самых частых офисных рутин: сбор данных из разных вкладок. Он создаёт итоговый лист, проходит по исходным листам, пропускает заголовки, находит последнюю строку и вставляет всё в общий отчёт.
Начните с копии файла. Проверьте структуру листов. Запустите макрос. Сверьте строки. И только потом переносите в рабочий файл.
В Telegram я оставлю файл с примером книги для тренировки: несколько листов с данными, готовая структура и код макроса. Можно открыть, посмотреть, повторить и адаптировать под свой отчёт.
Напишите в комментариях, из чего вам чаще приходится собирать общий отчёт: месяцы, филиалы, менеджеры, склады, расходы или заявки. По самым частым вариантам сделаю отдельные макросы.
Макрос решает — Excel, Word, PowerPoint и VBA без лишних слов.