Хитрости »
Основные понятия (22)
Сводные таблицы и анализ данных (7)
Графики и диаграммы (5)
Работа с VB проектом (11)
Power BI и Power Query (11)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (62)
Разное (37)

Как собрать данные с нескольких листов или книг?

Очень часто бывает необходимо собрать данные с нескольких листов одной книги или даже с листов нескольких книг. Например, каждую неделю мы получаем некие отчеты от отделов, которые необходимо собрать в одну общую таблицу для построения сводной таблицы. Или это могут быть некие книги прайсов по товарам от разных поставщиком, который опять же надо сначала объединить, а потом уже анализировать. Вручную делать это довольно муторно. И то, муторно это только для первых 20-ти листов/файлов, потом становится просто тошно. Поэтому решил поделиться решением, которое поможет собрать данные со всех листов книги, со всех листов всех указанных книг или только с указанных листов:

Приведенный выше код необходимо вставить в стандартный модуль(Что такое модуль? Какие бывают модули?). Выполнить его можно будет из этой книги нажатием клавиш Alt+F8. В появившемся окне выбрать Consolidated_Range_of_Books_and_Sheets и нажать Выполнить. Так же можно создать на листе кнопку и назначить ей данный макрос. Так же, если впервые работаете с макросами настоятельно рекомендую прочитать статью: Что такое макрос и где его искать?, а так же Почему не работает макрос?

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

  • Диапазон сбора данных - Если в окне выбора диапазона выбрать только одну ячейку, то данные будут собраны со всех листов книги/книг, начиная с этой ячейки и до последней ячейки листа.
    Если выбрать несколько ячеек, данные будут собраны только с указанного диапазона всех листов книги/книг.
  • Имя листа - Необязателен для указания. Если не указан - данные будут собраны со всех листов. Указать можно как точное соответствие имени листа, так и с частичным соответствием. Например, если в книгах для сбора данных необходимо собрать данные только с листа "Январь", то следует так и указать - "Январь". Если требуется собрать данные только с листов, начинающихся с "Продажи"("Продажи ЮГ", "Продажи НН", "Продажи Запад" и т.д.), то следует применить символ подстановки звездочку - "Продажи*". Если надо собрать с листов, содержащих в имени "продажи"("Итоговые продажи ЮГ", "Продажи НН", "Сезонные продажи" и т.д.), то указываем "*продажи*". Если надо собрать только с листа "Сезонные продажи", но известно, что вместо пробела может быть нижнее подчеркивание или тире("Сезонные продажи", "Сезонные_продажи", "Сезонные-продажи") или иной символ, то можно также применить звездочку - "Сезонные*продажи". Но если среди листов могут встречаться и такие как "Сезонные разовые продажи", "Сезонные корпоративные продажи" и т.п., но информацию с них собирать не надо, то можно применить вопросительный знак - "Сезонные?продажи". Вопросительный знак заменяет любой один символ, звездочка - любое количество любых символов.
  • Далее появится запрос: Вставлять только значения? - если выбрать Да, то в результирующий лист с листов будут вставлены исключительно значения ячеек (без формул, форматов, заливки ячеек, цвета шрифта и т.п.). Может пригодится, если на листах для сбора записаны формулы, ссылающиеся на другие листы, книги, диапазоны. При обычном копировании может случиться так, что формула выдаст ошибку, т.к. в книге для вставки нет таких листов и диапазонов или данные расположены иначе. Если выбрать Нет, то все ячейки с листов на результирующий будут копироваться в точности как в исходных листах.
  • И последний запрос: Собрать данные с нескольких книг? - если выбрать Да, то появится диалоговое окно выбора файлов. Надо указать все файлы, данные с которых необходимо собрать. Если выбрать Нет, то данные будут собираться с листов только активной книги.

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

Если после сбора данных обнаружили, что после каждого файла/листа много пустых строк, то следует найти в коде строку:
lLastrow = .Cells(1, 1).SpecialCells(xlLastCell).Row
и заменить её на строку примерно следующего содержания:
lLastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
где 1 - это номер столбца на листах данных, в котором искать последнюю заполненную ячейку.
Актуально это для файлов с одинаковой структурой. Например, если сбор идет с листов по продажам, то вполне может быть такое, что в столбце 1 может не быть данных. Поэтому следует определить номер столбца, в котором наполнение данных максимально. Например, это может быть столбец с наименованиями товара или с суммами. Если это столбец D, то следует строку записать так:
lLastrow = .Cells(.Rows.Count, 4).End(xlUp).Row 'ищем последнюю строку в 4-м столбце
Подробнее про определение последней строки можно прочитать в статье: Как определить последнюю ячейку на листе через VBA?

Важное замечание: Если вы используете Excel 2007 и выше и файлы для сбора данных тоже в этом формате, то следует скачанный файл сначала сохранить в формат "Книга Excel с поддержкой макросов(.xlsm)", закрыть и открыть заново. Иначе есть шанс получить ошибку при сборе данных, т.к. Excel будет в режиме совместимости и не сможет поместить на результирующий лист более 65536 строк.

Скачать пример:

  Tips_Macro_Consolidated.xls (50,0 KiB, 25 921 скачиваний)

Также см.:
Сбор данных с нескольких листов/книг
Как объединить несколько текстовых файлов в один?
Просмотреть все файлы в папке
План-фактный анализ в Excel при помощи Power Query


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Печать Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных ссылки
Обсуждение: 314 комментариев
  1. Владимир:

    Благодарю Вас за код!
    Собрал данные за долю секунды. Вручную бы мучался несколько часов.

    0
  2. Полина:

    Добрый день!
    насколько я поняла, этот макрос просто добавляет снизу один лист к другому. у меня есть одна таблица, но в двух файлах, и, соответственно, часть заполнена в одной, часть в другой. Например, в одном файле строка 1|_|3|_|5|, а в другой таблице в той же строке _|2|_|4|_
    можно ли соединить эти таблицы, чтобы недостающая информация в одной таюблице дополнилась инф=ей из другой?

    0
  3. Энзо:

    Добрый день, Дмитрий.

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

    0
    • Попробуйте эту строку:
      .Range(sCopyAddress).Copy
      записать так:
      .Range(sCopyAddress).SpecialCells(xlVisible).Copy

      0
      • Энзо:

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

        0
        • Для снятия защиты можно использовать такой код:

          1234 - пароль.

          0
          • Энзо:

            Дмитрий, огромное спасибо!! По коду посмотрел вставил после строки Dim Paste Values As Boolean (логику макросов только изучаю). Ругается (duplicate declaration in current scope) и выделяет wsSh As Object. Я правильно вас понял, что этот код нудно в ваш код добавить ?

            Или код который открывает пароли с книг нужно давать отдельным макросом?

            0
          • Энзо:

            Все еще голова кругом, Дмитрий не подскажите как можно в ваш код , добавить

            Dim wsSh As Object
            For Each wsSh In ActiveWorkbook.Sheets
            wsSh.Unprotect "1234"
            Next wsSh
            или я не туда пошел...

            0
  4. Роман:

    Дмитрий, спасибо очень полезно и занятно! Подскажите пожалуйста, а как переместить данные не на новый созданный лист а на существующий?

    0
  5. Олег:

    Добрый день, подскажите как добавить фильтр чтоб не все данные с листов копировать в один а нужные мне...
    Например: нужно собирать только те массивы строк в которых в первой ячейке будет слово "........"

    0
  6. Наталья:

    Добрый день. У меня почему-то не получилось :-( после кнопки выполнить выкидывает в модуль и выдает ошибки, в коде выделяет синим

    0
  7. Александр:

    Спасибо за макрос. Но почему то вставляет данные после 4000 строки, почитал все коменты такого не нашел.

    0
    • Александр, прочитали не все комменты. Ситуация уже обсуждалась. Вы делаете сбор данных на существующий лист, в котором до этого уже были данные и скорее всего были потом удалены. Полистайте комментарии, там есть ситуация про пропуски в виде пустых строк между данными - причина так же самая.

      0
Поделитесь своим мнением

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


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Тренинги

Заказать
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2017 Excel для всех  Войти
Авторизация
*
*
Регистрация
*
*
*
Пароль не введен
*
captcha
Генерация пароля