Макрос объединение нескольких листов в один excel

Сборка листов из разных книг в одну

Предположим, имеется куча книг Excel, все листы из которых надо объединить в один файл. Копировать руками долго и мучительно, поэтому имеет смысл использовать несложный макрос.

Открываем книгу, куда хотим собрать листы из других файлов, входим в редактор Visual Basic сочетанием клавиш Alt+F11 одноименной кнопкой на вкладке Разработчик (Developer — Visual Basic), добавляем новый пустой модуль (в меню Insert — Module) и копируем туда текст вот такого макроса:

 
Sub CombineWorkbooks()
    Dim FilesToOpen
    Dim x As Integer

    Application.ScreenUpdating = False  'отключаем обновление экрана для скорости
    
    'вызываем диалог выбора файлов для импорта
    FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="All files (*.*), *.*", _
      MultiSelect:=True, Title:="Files to Merge")

    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "Не выбрано ни одного файла!"
        Exit Sub
    End If
    
    'проходим по всем выбранным файлам
    x = 1
    While x <= UBound(FilesToOpen)
        Set importWB = Workbooks.Open(Filename:=FilesToOpen(x))
        Sheets().Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        importWB.Close savechanges:=False
        x = x + 1
    Wend

    Application.ScreenUpdating = True
End Sub

После этого можно вернуться в Excel и запустить созданный макрос на вкладке Разработчик кнопкой Макросы (Developer — Macros) или нажав Alt+F8. Отобразится диалоговое окно открытия файла, где необходимо указать один или несколько (удерживая Ctrl или Shift) файлов, листы из которых надо добавить к текущей книге — и задача решена!

Ссылки по теме

Что такое макросы, куда вставлять код макроса на Visual Basic
Автоматическая сборка заданных листов из заданных книг с помощью надстройки PLEX
Автоматическая сборка данных с нескольких листов на один итоговый лист с помощью надстройки PLEX

Источник

Excel macro that allows you to select multiple workbooks and have all of their worksheets automatically combined into a single workbook.

This macro will add the worksheets to a newly created workbook and not to the currently open/visible workbook. This allows you to do whatever you want with the new workbook without having to worry about the contents of the current workbook.

This macro allows you to select as many files as you want to combine and you do it all through the regular «Select File» dialogue box, which makes it really easy to use.

You do not have to change anything for this macro to work, just run it.

Where to Install the Macro: Module

Sub Combine_Workbooks_Select_Files_1()

    Dim Fnum As Long
    Dim mybook As Workbook
    Dim BaseWb As Workbook
    Dim CalcMode As Long
    Dim FName As Variant
    Dim wSheet As Worksheet


    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With

    'Get the files to combine
    FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)

    If IsArray(FName) Then

        'Create the workbook to store the combined files
        Set BaseWb = Workbooks.Add(xlWBATWorksheet)

        'Loop through the files to combine
        For Fnum = LBound(FName) To UBound(FName)
            Set mybook = Nothing
            On Error Resume Next
            Set mybook = Workbooks.Open(FName(Fnum))
            On Error GoTo 0
            If Not mybook Is Nothing Then
                On Error Resume Next

                'Add the worksheets to the new workbook
                For Each wSheet In mybook.Worksheets

                    wSheet.Move Before:=BaseWb.Sheets(1)

                Next wSheet

                mybook.Close savechanges:=False

                On Error GoTo 0

            End If

        Next Fnum

        'Delete a default worksheet that was added to the new workbook.
        BaseWb.Sheets(BaseWb.Sheets.Count).Delete

    End If

ExitTheSub:

    'Reset the options for display, alerts, and calculations.
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
        .DisplayAlerts = True
    End With

End Sub

Excel VBA Course — From Beginner to Expert

200+ Video Lessons
50+ Hours of Instruction
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Обьединение нескольких листов данных в один макросом

11.02.2013, 18:24. Показов 41061. Ответов 4

Метки нет (Все метки)

Здравствуйте, подскажите пожалуйста.
У меня есть 30 листов с данными
Мне нужно объединить их в один лист. Как мне записать макрос?
Я копирую данные из первого листа выделяю до последней ячейки с данными, вставляю в общий лист, далее перехожу на новый лист, так же копирую данные, выделяю до последней ячейки. Но у меня кол-во ячеек в каждом листе может меняться, как правильно прописать код?

Кликните здесь для просмотра всего текста

Visual Basic

    Rows("2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("Общий").Select
    Range("A2").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("PIL2PIL_2").Select
    Rows("2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("Общий").Select

Watcher_1

356 / 162 / 27

Регистрация: 21.06.2011

Сообщений: 350

11.02.2013, 21:29

Как смог, понял структуру вашего файла…
Кол-во заполненных строк проверяется по столбцу А

Visual Basic

Sub m()
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "Общий" Then
           myR_Total = Sheets("Общий").Range("A" & Sheets("Общий").Rows.Count).End(xlUp).Row
           myR_i = Sheets(i).Range("A" & Sheets(i).Rows.Count).End(xlUp).Row
           Sheets(i).Rows("1:" & myR_i).Copy Destination:=Sheets("Общий").Range("A" & myR_Total + 1)
        End If
    Next
End Sub

Igor_Tr

4377 / 661 / 36

Регистрация: 17.01.2010

Сообщений: 2,134

11.02.2013, 23:24

Как раз что-то похожее делал для себя. Кидаю вариант и немного обясню. Предполагается, что у меня должен бы быть лист Общих данных «GenExMan» (в даном случае — обобщенный лист счетов с данными). Этот лист (раньше) был разделен так, что для каждого счета создался отдельный лист с именем счета и данными по этому счету. После создания отчета/обработки/ каждого счета, мне нужно опять опять все собрать на новый лист «MergeRecords» и потом вернуть на Android. Представленный код — это фрагмент общего, который все и собирает.
И еще — тут использована функция проверки наличия листа в книге. Если нужно будет — докину (хотя я ее где-то здесь уже выставлял). Старые коментарии — украинским, новые (для Вас) — русским. У меня все работает как часы. Пробуйте.

Visual Basic

Sub ConsolidateAllSheet()
    Application.ScreenUpdating = False
    ' Проверяем наличие основного листа, с которого будет получено _
          список уникальных названий счетов
    If Not fun_SheetExists("GenExMan") Then ' 
        MsgBox "АРКУШ   " & "GenExMan" & "   ВІДСУТНІЙ."
        Exit Sub
            Else: Sheets("GenExMan").Select
    End If
    ' Проверяем наличие в книге листа, в который будем собирать _
           нужные нам листы.
    If Not fun_SheetExists("MergeRecords") Then
        Sheets.Add ' создаем
            With ActiveSheet
                .Name = "MergeRecords" ' именуем
                .Tab.ColorIndex = 10 ' красим ярлык
            End With ' ActiveSheet
        Sheets("GenExMan").Select
             ' Очищаем "собирательный лист" для новых данных
            Else: Sheets("MergeRecords").Cells.Delete ' _
                                    вивільнюємо аркуш для перезапису.
    End If
    Application.ScreenUpdating = True
    
    Set Dict = CreateObject("Scripting.dictionary") ' создаем словарь
 
    With Sheets("GenExMan")
        Set mRng = Range(.Cells(1, 1), _
                        Cells(1, .UsedRange.Columns.Count))
        counter = 0
        For Each CurrCell In mRng
            counter = counter + 1
            ReDim Preserve arr1(counter)
            arr1(counter) = mRng.Cells(counter) ' _
                                            Назви полів - в масив.
        Next ' CurrCell
        With Sheets("MergeRecords")
            .Cells(1).Resize(1, UBound(arr1)).Value = arr1
        End With
        Erase arr1 ' вивільнення пам'яті.
        With mRng
           ' Определяем в общем листе номер столбца с названиями счетов
            mstr = "Account" ' Знаходимо поле з значенням _
                         "Account" - це будуть ключі Dict.
            Set CurrCell = .Find(What:=mstr, _
                            LookAt:=xlWhole, SearchFormat:=False)
                If mRng Is Nothing Then
                    MsgBox "Не   знайдено !": Exit Sub
                        Else: mCol = CurrCell.Column
                End If
        End With ' mRng
        ' Выбираем уникальные названия счетов
        For i = 2 To .UsedRange.Rows.Count ' отримую _
                                       перелік назв рахунків.
            If Not Dict.exists(Trim(.Cells(i, mCol).Value)) Then
                Dict.Add key:=Trim(.Cells(i, mCol).Value), _
                                        Item:=Trim(.Cells(i, mCol).Value)
            End If
        Next 'i
    End With
    ' Заливаем уникальные названия счетов в массив genKeys. _
           В данном случае ними будут и ключи, и значения словаря. 
    genKeys = Dict.keys ' ключі словника - в масив.
    Set Dict = Nothing ' видаляємо словник.
    ' Ниже - просто проверка при отладке.
''--Results--Verification------------------------
'Stop
'    Application.SendKeys "^g^a {Del}"
'    For i = LBound(genKeys) To UBound(genKeys)
'        Debug.Print genKeys(i)
'    Next 'i
''==End==Verification======================
 
     ' А вот этот коротенький код уже собственно перебирает _
       раздельные листы счетов, заганяет по каждому листу _
       значения в массив  mArr ...
'Stop
    For i = LBound(genKeys) To UBound(genKeys)
        Erase mArr ' очищаємо масив
        With Sheets(genKeys(i))
            mArr = Range(.Cells(2, 1), _
                .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Value
        End With
    '... и, наконец, выгружает массив mArr  на сборочный лист "MergeRecords".
'Stop
        With Sheets("MergeRecords")
            r = .UsedRange.Rows.Count + 1
            .Cells(r, 1).Resize(UBound(mArr, 1), _
                    UBound(mArr, 2)).Value = mArr
        End With
    Next 'i
 Stop
        
End Sub

0 / 0 / 0

Регистрация: 22.01.2016

Сообщений: 1

22.01.2016, 20:29

Igor_Tr, вечер добрый, не могли бы вы подсказать как использовать ваш макрос в своей таблице?
Лист1 — 44 составляющих для отбора по всем листам ( всего листов 160)

Nyusya

0 / 0 / 0

Регистрация: 13.10.2015

Сообщений: 1

08.08.2016, 09:43

Сообщение от Watcher_1

Как смог, понял структуру вашего файла…
Кол-во заполненных строк проверяется по столбцу А

Visual Basic

Sub m()
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "Общий" Then
           myR_Total = Sheets("Общий").Range("A" & Sheets("Общий").Rows.Count).End(xlUp).Row
           myR_i = Sheets(i).Range("A" & Sheets(i).Rows.Count).End(xlUp).Row
           Sheets(i).Rows("1:" & myR_i).Copy Destination:=Sheets("Общий").Range("A" & myR_Total + 1)
        End If
    Next
End Sub

Доброго дня!
Случайно забрела сюда в поиске макроса по сбору данных и нашла именно то, что нужно.
Только два вопросика, как в данный макрос можно добавить имена листов, откуда копируются данные и каеую команду добавить, чтобы вставлялись значения вместо формул. Заранее благодарю!

IT_Exp Эксперт 87844 / 49110 / 22898 Регистрация: 17.06.2006 Сообщений: 92,604	08.08.2016, 09:43
5

Источник

6 примеров — как консолидировать данные и объединить листы Excel в один

В статье рассматриваются различные способы объединения листов в Excel в зависимости от того, какой результат вы хотите получить:

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

Сегодня мы займемся проблемой, с которой ежедневно сталкиваются многие пользователи Excel, — как объединить листы Excel в один без использования операций копирования и вставки. Рассмотрим два наиболее распространенных сценария: объединение числовых данных (сумма, количество, среднее и т. д.) и объединение листов ( то есть копирование данных из нескольких листов в один).

Вот что мы рассмотрим в этой статье:

Объединение при помощи стандартного инструмента консолидации.
Как копировать несколько листов Excel в один.
Как объединить листы с различным порядком столбцов.
Объединение только определённых столбцов из нескольких листов
Слияние листов в Excel с использованием VBA
Как объединить два листа в один по ключевым столбцам

Консолидация данных из нескольких листов на одном.

Самый быстрый способ консолидировать данные в Excel (в одной или нескольких книгах) — использовать встроенную функцию Excel Консолидация.

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

Как вы видите на скриншоте ниже, четыре объединяемых листа имеют схожую структуру данных, но разное количество строк и столбцов:

Чтобы объединить всю эту информацию на одном листе, выполните следующие действия:

Правильно расположите исходные данные. Чтобы функция консолидации Excel работала правильно, убедитесь, что:
- Каждый диапазон (набор данных), который вы хотите объединить, находится на отдельном листе. Не помещайте данные на лист, куда вы планируете выводить консолидированные данные.
- Каждый лист имеет одинаковый макет, и каждый столбец имеет заголовок и содержит похожие данные.
- Ни в одном списке нет пустых строк или столбцов.
Запустите инструмент «Консолидация». На новом листе, где вы планируете поместить результаты, щелкните верхнюю левую ячейку, начиная с которой должны отображаться консолидированные данные, затем на ленте перейдите на вкладку «Данные» и нажмите кнопку «Консолидация».

Совет. Желательно объединить данные в пустой лист. Если на вашем основном листе уже есть данные, убедитесь, что имеется достаточно места (пустые строки и столбцы) для записи результатов.

Настройте параметры консолидации. Появляется диалоговое окно «Консолидация», и вы делаете следующее:
- В поле «Функция» выберите одну из функций, которую вы хотите использовать для консолидации данных (количество, среднее, максимальное, минимальное и т. д.). В этом примере мы выбираем Сумма.
- В справочном окне, нажав в поле Ссылка на значок , выберите диапазон на первом листе. Затем нажмите кнопку «Добавить», чтобы присоединить его к списку диапазонов. Повторите этот шаг для всех листов, которые вы хотите объединить.

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

Настройте параметры обновления. В том же диалоговом окне Консолидация выберите любой из следующих параметров:
- Установите флажки «Подписи верхней строки» и / или «Значения левого столбца» в разделе «Использовать в качестве имён», если вы хотите, чтобы заголовки строк и / или столбцов исходных диапазонов были также скопированы.
- Установите флажок «Создать связи с исходными данными», если нужно, чтобы консолидированные данные обновлялись автоматически при изменении исходных таблиц. В этом случае Excel создаст ссылки на ваши исходные листы, а также схему, как на следующем скриншоте:

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

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

Если вы хотите объединить листы в Excel, просто скопировав и объединив их содержимое, вариант консолидации не подходит. Чтобы объединить всего парочку из них, создав как бы единый массив данных, то вам из стандартных возможностей Excel не подойдёт ничего, кроме старого доброго копирования / вставки.

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

Поэтому для подобных задач рекомендую использовать один из перечисленных далее нестандартных методов для автоматизации слияния.

Как скопировать несколько листов Excel в один.

Как мы уже убедились, встроенная функция консолидации умеет суммировать данные из разных листов, но не может объединять их путем копирования данных на какой-то итоговый лист. Для этого вы можете использовать один из инструментов слияния и комбинирования, включенных в надстройку Ultimate Suite для Excel.

Для начала давайте будем исходить из следующих условий:

Структура таблиц и порядок столбцов на всех листах одинаковы.
Количество строк везде разное.
Листы могут в будущем добавляться или удаляться.

Итак, у вас есть несколько таблиц, содержащих информацию о различных товарах, и теперь вам нужно объединить эти таблицы в одну итоговую, например так, как на рисунке ниже:

Три простых шага — это все, что нужно, чтобы объединить выбранные листы в один.

1. Запустите мастер копирования листов.

На ленте перейдите на вкладку AblebitsData, нажмите «Копировать листы (Copy Sheets)» и выберите один из следующих вариантов:

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

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

1. Выберите листы и, при необходимости, диапазоны для объединения.

Мастер копирования листов отображает список всех имеющихся листов во всех открытых книгах. Выберите те из них, которые хотите объединить, и нажмите « Далее».

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

В этом примере мы объединяем первые три листа нашей книги:

Совет. Если рабочие листы, которые вы хотите объединить, находятся в другой книге, которая в данный момент закрыта, нажмите кнопку «Добавить файлы …» , чтобы найти и открыть эту книгу.

2. Выберите, каким образом произвести объединение.

На этом этапе вы должны настроить дополнительные параметры, чтобы ваша информация была объединена именно так, как вы хотите.

Как вставить :

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

Как расположить :

Разместите скопированные диапазоны один под другим – то есть вертикально.
Расположить скопированные диапазоны рядом – то есть по горизонтали.

Как скопировать :

Сохранить форматирование – понятно и очень удобно.
- Разделить скопированные диапазоны пустой строкой – выберите этот вариант, если вы хотите добавить пустую строку между сведениями, скопированными из разных листов. Так вы сможете отделить их друг от друга, если это необходимо.
- Скопировать таблицы вместе с их заголовками. Установите этот флажок, если хотите, чтобы заголовки исходных таблиц были включены в итоговый лист.

На скриншоте ниже показаны настройки по умолчанию, которые нам подходят:

Нажмите кнопку «Копировать (Copy)», и у вас будет содержимое трех разных листов, объединенное в один итоговый, как показано в начале этого примера.

Быть может, вы скажете, что подобную операцию можно произвести путем обычного копирования и вставки. Но если у вас будет десяток или более листов и хотя бы несколько сотен строк на каждом из них, то это будет весьма трудоемкой операцией, которая займет довольно много времени. Да и ошибки вполне вероятны. Использование надстройки сэкономит вам много времени и избавит от проблем.

Как объединить листы Excel с различным порядком столбцов.

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

Используем инструмент объединения листов Combine Sheets.

Запускаем надстройку через меню Ablebits Data – Combine Sheets.

Выбираем и отмечаем галочками те листы, данные с которых мы хотим объединить. Затем программа анализирует их и предлагает нам список найденных столбцов с указанием того, сколько раз столбец с подобным названием был обнаружен.

Вы должны указать те столбцы, данные из которых вы хотели бы объединить. Можете выбрать их все, можете – только самые важные.

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

Также можно указать, что необходимо сохранить исходное форматирование, если оно уникально в каждой таблице. Так вам, кстати, будет проще определить, откуда появились сведения в общем массиве, какая таблица является их источником.

И данные будут идеально скомпонованы по заголовкам столбцов:

Мы получили своего рода сводную таблицу с необходимой информацией.

Объединение определенных столбцов из нескольких листов.

А вот, как мне кажется, наиболее часто встречающаяся ситуация:

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

Запустите мастер объединения листов, как мы это делали в предыдущем примере, укажите нужные, а затем выберите соответствующие столбцы. Да, это так просто!

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

Эти примеры продемонстрировали только несколько инструментов слияния данных, но это еще не все! Немного поэкспериментировав, вы увидите, насколько полезны и удобны все функции, включенные в пакет.

Полнофункциональная ознакомительная версия Ultimate Suite доступна для загрузки в по этой ссылке.

Слияние листов в Excel с помощью кода VBA

Если вы опытный пользователь Excel и чувствуете себя комфортно с макросами и VBA, вы можете объединить несколько листов Excel в один, используя какой-нибудь сценарий.

Для этого на вкладке Разработчик (Developer) нажмите кнопку Visual Basic или воспользуйтесь сочетанием клавиш Alt+F11. В открывшемся окне добавьте новый модуль через меню Insert — Module и скопируйте туда текст вот такого макроса:

Sub CopyDataWithHeaders()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim shLast As Long
    Dim CopyRng As Range
    Dim StartRow As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Delete the sheet "RDBMergeSheet" if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add a worksheet with the name "RDBMergeSheet"
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "RDBMergeSheet"

    'Fill in the start row
    StartRow = 2

    'loop through all worksheets and copy the data to the DestSh
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> DestSh.Name Then

'Copy header row, change the range if you use more columns
If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
  sh.Range("A1:Z1").Copy DestSh.Range("A1")
End If

            'Find the last row with data on the DestSh and sh
            Last = LastRow(DestSh)
            shLast = LastRow(sh)

            'If sh is not empty and if the last row >= StartRow copy the CopyRng
            If shLast > 0 And shLast >= StartRow Then

                'Set the range that you want to copy
                Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

                'Test if there enough rows in the DestSh to copy all the data
                If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                    MsgBox "There are not enough rows in the Destsh"
                    GoTo ExitTheSub
                End If

                'This example copies values/formats, if you only want to copy the
                'values or want to copy everything look below example 1 on this page
                CopyRng.Copy
                With DestSh.Cells(Last + 1, "A")
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False
                End With

            End If

        End If
    Next

ExitTheSub:

    Application.Goto DestSh.Cells(1)

    'AutoFit the column width in the DestSh sheet
    DestSh.Columns.AutoFit

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function


Function LastCol(sh As Worksheet)
    On Error Resume Next
    LastCol = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    On Error GoTo 0
End Function

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

В этой функции выполняется копирование данных со всех листов начиная со строки 2 и до последней строки с данными. Если шапка в ваших таблицах занимает две или более строки, то измените этот код, поставив вместо 2 цифры 3, 4 и т.д.:

    'Fill in the start row
    StartRow = 2

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

Ещё несколько интересных примеров кода VBA для объединения листов вашей рабочей книги вы можете найти по этой ссылке.

Как объединить два листа Excel в один по ключевому столбцу

Если вы ищете быстрый способ сопоставить и объединить данные из двух листов, вы можете либо использовать функцию Excel ВПР, либо воспользоваться мастером объединения таблиц Merge Two Tables.

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

Более подробно его работа рассмотрена в этой статье.

Мастер объединения двух таблиц также включен в Ultimate Suite for Excel, как и множество других полезных функций.

Вот как вы можете объединить листы в Excel. Я надеюсь, что вы найдете информацию в этом коротком руководстве полезной. Если у вас есть вопросы, не стесняйтесь оставлять их в комментариях.

Быстрое удаление пустых столбцов в Excel — В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых…

Как быстро объединить несколько файлов Excel — Мы рассмотрим три способа объединения файлов Excel в один: путем копирования листов, запуска макроса VBA и использования инструмента «Копировать рабочие листы» из надстройки Ultimate Suite. Намного проще обрабатывать данные в…

Как работать с мастером формул даты и времени — Работа со значениями, связанными со временем, требует глубокого понимания того, как функции ДАТА, РАЗНДАТ и ВРЕМЯ работают в Excel. Эта надстройка позволяет быстро выполнять вычисления даты и времени и без особых…

Как найти и выделить уникальные значения в столбце — В статье описаны наиболее эффективные способы поиска, фильтрации и выделения уникальных значений в Excel. Ранее мы рассмотрели различные способы подсчета уникальных значений в Excel. Но иногда вам может понадобиться только просмотреть уникальные…

Как получить список уникальных значений — В статье описано, как получить список уникальных значений в столбце с помощью формулы и как настроить эту формулу для различных наборов данных. Вы также узнаете, как быстро получить отдельный список с…

6 способов быстро транспонировать таблицу — В этой статье показано, как столбец можно превратить в строку в Excel с помощью функции ТРАНСП, специальной вставки, кода VBA или же специального инструмента. Иначе говоря, мы научимся транспонировать таблицу.…

Как объединить две или несколько таблиц в Excel — В этом руководстве вы найдете некоторые приемы объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается на одном…

Источник

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

В нашей повседневной работе мы можем столкнуться с проблемой объединения сотен листов или книг в один лист для анализа данных, что занимает много времени, если вы используете команду «Копировать и вставить» в Excel. В этом уроке я дам несколько советов по быстрому решению этой задачи.

БЫСТРАЯ НАВИГАЦИЯ

Объединение всех листов активной книги в один лист с помощью VBA
Объединение десятков листов или книг в один лист / книгу с помощью щелчков мышью
Объединение двух таблиц в одну и обновление по столбцу кликами
Скачать образец файла

Объединение всех листов активной книги в один лист с помощью VBA

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

1. Активируйте книгу, все листы которой вы хотите объединить, затем нажмите + ключи для открытия Microsoft Visual Basic для приложений окно.

2. Во всплывающем окне нажмите Вставить > Модули для создания нового скрипта модуля.

3. Скопируйте приведенный ниже код и вставьте его в скрипт.

Sub Combine()
'UpdatebyExtendoffice
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

4. Нажмите F5 key, то все данные на листах были объединены в новый лист с именем Combined, который помещается перед всеми листами.

Вы хотите повысить зарплату и много времени проводить с семьей?

Вкладка Office повышает эффективность работы Microsoft Office на 50% прямо сейчас

Невероятно, но работать с двумя или более документами проще и быстрее, чем над одним.

По сравнению с известными браузерами инструмент с вкладками в Office Tab более мощный и эффективный.

Сократите для вас сотни щелчков мыши и набора текста с клавиатуры каждый день, попрощайтесь с рукой мыши.

Если вы обычно работаете с несколькими документами, вкладка Office поможет вам сэкономить время.

30— дневная бесплатная пробная версия, кредитная карта не требуется.

Узнать большеСкачать сейчас

Объединение десятков листов или книг в один лист / книгу с помощью щелчков мышью

С помощью VBA вы можете объединять листы только в активной книге, но как вы можете объединить листы из книг в лист или книгу?

Объединение листов из книг в один лист

Объединение листов из книг в одну книгу

Для решения этой задачи и удовлетворения других требований по комбинации листов Сочетать функция была разработана с четырьмя комбинированными сценариями:

Объедините несколько листов или книг на один лист
Объедините несколько листов или книг в одну книгу
Объединить листы с одинаковыми именами в один лист
Объединяйте значения на листах или в книгах на один лист

Здесь в качестве примера рассматривается второй вариант:

Объедините несколько листов или книг в одну книгу

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Активируйте Excel, щелкните Кутулс Плюс > Сочетать, появится диалоговое окно, напоминающее вам, что книги, которые вы хотите объединить, необходимо закрыть. Нажмите OK для продолжения.

2. в Объедините рабочие листы, шаг 1 диалог, проверьте Объедините несколько листов из книг в одну книгу вариант. Нажмите Download для перехода к следующему шагу мастера.

3. Нажмите Добавить > Файл or Папка чтобы добавить книги, которые вы хотите объединить, в Список рабочих книг панели, затем вы можете указать, какой лист будет объединен, проверив имена в Список рабочих листов панель. Нажмите Download перейти к последнему шагу мастера.

4. На этом шаге укажите нужные вам настройки. Затем нажмите Завершить.

5. Появится окно, в котором вы выберете папку для размещения объединенной книги, затем нажмите Сохраните.

Теперь книги объединены в одну книгу. И в начале всех листов мастер-лист с именем Kutools for Excel также создается, в котором перечислены некоторые сведения о листах и ссылки для каждого листа.

Демонстрация: объединение листов / книг в один лист или книгу

Kutools for Excel:200 + полезных удобных инструментов, упрощающих сложные задачи в Excel в несколько кликов.

Попрощайтесь с мышиной рукой и шейным спондилезом

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

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

Ежедневно повышайте производительность на 80% для более чем 110000 высокоэффективных людей, включая вас.

Больше не мучиться мучительными формулами и VBA, дайте своему мозгу отдых и радостное рабочее настроение.

30— дневная бесплатная пробная версия со всеми функциями, 30день назад деньги без причины.

Лучшее тело создает лучшую жизнь.

Объединение двух таблиц в одну и обновление по столбцу кликами

Если вы хотите объединить две таблицы в одну и обновить данные на основе столбца, как показано на скриншоте ниже, вы можете попробовать Слияние таблиц полезности Kutools for Excel.

Kutools for Excel: более 200 удобных надстроек Excel для упрощения сложных задач в несколько кликов в Excel

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Нажмите Кутулс Плюс > Слияние таблиц включить Слияние таблиц Мастер.

2. На шаге 1 мастера вам необходимо отдельно выбрать диапазоны основной таблицы и таблицы поиска. Затем нажмите Download.

3. Отметьте ключевой столбец, на основе которого вы хотите обновить данные в основной таблице. Нажмите Download.

4. Затем отметьте столбцы в основной таблице, в которых вы хотите обновить данные на основе таблицы поиска. Нажмите Download.

5. На шаге 4 мастера отметьте столбцы, которые вы хотите добавить из справочной таблицы в основную таблицу. Нажмите Download.

6. На последнем этапе мастера укажите необходимые параметры настройки. Затем нажмите Завершить.

Теперь в основной таблице были обновлены данные и добавлены новые данные на основе таблицы поиска.

Демонстрация: объединение листов / книг в один лист или книгу

Kutools for Excel:200 + полезных удобных инструментов, упрощающих сложные задачи в Excel в несколько кликов.

Скачать образец файла

Объединение данных с нескольких листов

Смотрите также: mass(j2) If s1Tumatayev Dim secondBook Astw333k выше делает примерно что копировать, определемЧитаем Правила форума, я должен изменитьКодEnd With 1).SpecialCells(xlLastCell).Row + vbYesNo, «Excel-VBA») Object, lCalc AsНа конечном листе щелкнитеПуть к файлу вводится Убедитесь, что справа иПримечание:Z, Z, > 0 Then: файл Workbook Dim thirdBook

: Как тянуть один тоже самое, но куда копировать, копируем. создаём свою тему, только эту строку?End IfiLastColumn = .Cells.SpecialCells(xlLastCell).Column = vbYes) Long, lCol As верхнюю левую ячейку в поле снизу достаточно свободныхМы стараемся какпоиском занималась вчера ws2.Cells(i1, 6) =chumich As Workbook Set лист на лист на автомате.Остальное без данных прикладываем файл сl = .Cells.Find(«*»,

вставить строкуsCopyAddress = .Range(.Cells(iBeginRange.Row,’Запрос сбора данных Long области, в которойВсе ссылки ячеек для консолидированных можно оперативнее обеспечиватьНе подскажите, как s1 Else ws2.Cells(i1,: Вы немного переоценили secondBook = ActiveWorkbook в другом файлеЕсли с данными не сделать - примером. Эта тема , xlFormulas, 1,КодEnd If

iBeginRange.Column), .Cells(lLastrow, iLastColumn)).Address с книг(если НетDim oAwb As

Консолидация по расположению

требуется разместить консолидированные. данных. вас актуальными справочными сделать так что 6) = «Empty» возможности быстродействия компьютера.

Set firstBook = понятно (функция importrange), конечно повезло по как определять, как закрыта
1, 2, 3,migoCase Else ‘собираем — то сбор String, sCopyAddress As

 данные.После добавления данных изНа вкладке материалами на вашем бы этот макрос
Debug.Print i1; End Вы хотите чтобы Workbooks.Open(«C:\ALL.xlsx») Set thirdBook а вот как расположению и вообще… копировать — этоПредположим, имеется куча книг
4, 5, 6,: Здравствуйте! данные с фиксированного идет с активной
String, sSheetName AsПримечание:

всех исходных листовДанные языке. Эта страницаработал с конкретной
If End If он произвел 2 = Workbooks.Open(«C:\File.xlsx») Dim их потом объединитьDavletshinaY сейчас сказать нельзя.

Консолидация по категории

Excel, все листы 7, 8, 9).RowПрошу Вашей помощи диапазона книги) String Убедитесь, что справа и и книг нажмитев группе переведена автоматически, поэтому книгой? Next Debug.Print Debug.Print 669 653 824 mass(1099, 2) For не понимаю: Здравствуйте,

Hugo из которых надо
+ 1 в решении следующейsCopyAddress = iBeginRange.AddressIf MsgBox(«Собрать данныеDim lLastrow As

 снизу достаточно свободных кнопкуРабота с данными ее текст можетТ.е. у меня
«время выполнение=»; (Timer операций сравнения + i = 1Gustavнеужели кроме как: А то вот объединить в один
или еще что-то? задачи:End Select с нескольких книг?»,
Long, lLastRowMyBook As ячеек для консолидированныхОКнажмите кнопку содержать неточности и есть книга с — dt1)  вставка значений и To 1099 For: Надо создать вертикальный
с помощью макроса такой код - файл. Копировать рукамиPoltavaесть книга эксельlLastRowMyBook = wsDataSheet.Cells.SpecialCells(xlLastCell).Row vbInformation + vbYesNo,

Long, li As данных..Консолидация
грамматические ошибки. Для макросами, в ней 1; » sek» просто не можете j = 1 массив — перечислить

 нельзя решить задачу подойдёт? :) долго и мучительно,: Неправильно поняли! ничего состоящая из 12 + 1 «Excel-VBA.ru») = vbYes Long, iLastColumn As

Консолидация по расположению

На вкладкеПримечание:. нас важно, чтобы есть разные данные »21sek »надо доработать дождаться, когда он

To 2 mass(i, несколько диапазонов через объединения листов вOption Explicit поэтому имеет смысл
менять не надо! листов.’вставляем имя книги, Then Integer

Данные Любые названия, не совпадающиеВыберите в раскрывающемся списке эта статья была ( в том
для объединенных ячеек закончит работу А j) = firstBook.Sheets(1).Cells(i, точку с запятой один?Sub tt() использовать несложный макрос.
макрос сам перебираетна каждом из с которой собраныavFiles = Application.GetOpenFilename(«Excel
Dim wsSh Asв разделе с названиями вфункцию вам полезна. Просим

числе будет и End Sub так всё работает j) Next Next
в фигурных скобках:Спасибо.Dim sh AsОткрываем книгу, куда хотим все листы в этих листов находится

Консолидация по категории

данные files(*.xls*),*.xls*», , «Выбор Object, wsDataSheet AsСервис других исходных областях,, которую требуется использовать вас уделить пару этот новый макрос).Tumatayev правильно. Кроме того, secondBook.Sheets(1).Activate secondBook.Sheets(1).Range(«A1:D31673»).Copy thirdBook.Sheets(1).Activate={IMPORTRANGE(…Лист1!…);IMPORTRANGE(…Лист2!…);…IMPORTRANGE(…Лист11!…);IMPORTRANGE(…Лист12!…)}Yoooo Worksheet, rr As собрать листы из книге будь их

данные, которые надоIf lCol Then
файлов», , True) Object, bPolyBooks Asнажмите кнопку могут привести к для консолидации данных.

 секунд и сообщить,А есть книга: у вас в thirdBook.Sheets(1).Range(«B1»).Select ActiveSheet.Paste thirdBook.Sheets(1).Range(«B1:B31673»).Copy
tw333k: Нужно объединить таблицы Range, l As других файлов, входим там 3, 5 совместить на отдельном wsDataSheet.Cells(lLastRowMyBook, 1).Resize(Range(sCopyAddress).Rows.Count).Value =
If VarType(avFiles) = Boolean, avFilesКонсолидация появлению в консолидированных
Выделите на каждом листе помогла ли она с данными, вshanemac51 таблице листа 2 thirdBook.Sheets(1).Range(«A1»).Select ActiveSheet.Paste For: Спасибо, помогло. со всех листов Long в редактор Visual
или 10 листе этой же oAwb vbBoolean Then ExitDim wbAct As. данных отдельных строк нужные данные. вам, с помощью которой как раз

, идея бесподобна , есть значения, которых i1 = 1Tumatayev
в одну.For Each sh Basic (KuklP книги. В идеалеIf bPasteValues Then

 Sub WorkbookВыберите в раскрывающемся списке или столбцов.Путь к файлу вводится кнопок внизу страницы. нужно собрать данные. правда сначала мозг

support.office.com

объединение определенных листов в один (Макросы/Sub)

 нет в таблице To 31673 For: Доброе время суток!Проблема в том, In Worksheets
ALT+F11: Все верно — чтоб при ‘если вставляем только
bPolyBooks = TrueDim bPasteValues AsфункциюДля консолидации по расположению в поле Для удобства также
Как сделать так, остановился, пытаясь понять, листа 1 - k1 = 1 Еще раз нуждаюсь

 что порядок показателейIf sh.Name <>), добавляем новый пустой migo появлении в одном значенияlCol = 1 Boolean , которую требуется использовать диапазон данных наВсе ссылки приводим ссылку на чтобы макрос находился но потом дошло в этом случае To 1099 If в вашей помощи! в первой колонке "Лист2" Then модуль (в меню: Все сработало, спасибо из листов новых.Range(sCopyAddress).Copy ElseOn Error Resume для консолидации данных. каждом из исходных . оригинал (на английском в одной книге, Жаль, что рано в первой графе thirdBook.Sheets(1).Cells(i1, 1) = Нужен макрос для разный для каждойSet rr =Insert - Module еще раз!! данных, лист соwsDataSheet.Cells(lLastRowMyBook, 1).Offset(, lCol).PasteSpecial avFiles = Array(ThisWorkbook.FullName) NextУстановите флажки в группе листов должен иметьПосле добавления данных из языке) . а данные собирал "спасибо" поставил - таблицы листа 3 mass(k1, 1) Then объединение разных таблиц таблицы + иногда sh.[a1].CurrentRegion) и копируем туда Матрёна сводной информацией мог xlPasteValues End If 'Выбираем диапазон выборкиИспользовать в качестве имен формат списка без всех исходных листов Если данные, которые требуется только из другой? отзыв уже не остается значение второй Cells(i1, 1) = (книг) excel в встречаются показатели, которыхSet rr = текст вот такого: migo! обновляться.Else'отключаем обновление экрана, с книг , указывающие, где в пустых строк и и книг нажмите проанализировать, представлены наThe_Prist берет графы. Я попытаюсь mass(k1, 2) Next один как в нет в остальных rr.Offset(2, 0).Resize(rr.Rows.Count - макроса:См. вариант.Количество столбцов и.Range(sCopyAddress).Copy wsDataSheet.Cells(lLastRowMyBook, 1).Offset(, автопересчет формул иSet iBeginRange = исходных диапазонах находятся столбцов. кнопку нескольких листах или: Выбираете книгу иHugo121 облегчить компьютеру задачу, Next End SubИ примере. таблицах. 2, rr.Columns.Count) 'сдвиг Sub CombineWorkbooks() DimЯ - не их наименования в lCol) отслеживание событий Application.InputBox("Выберите диапазон сбора названия: Откройте каждый из исходных ОК в нескольких книгах, с неё будут , спасибо за код но возможно процесс получилась на третейЗаранее спасибо ктоTimSha выбора таблицы на FilesToOpen Dim x программист и с каждом листе совпадает,End If 'для скорости выполнения данных." & vbCrLfподписи верхней строки листов и убедитесь . их можно объединить собраны данные. цикла по объединенным всё равно будет таблице "ID, ID, откликнется)!: Для чего - 2 строки ниже As Integer Application.ScreenUpdating законами программирования в а кол-во строк End With кода и для & _ , в том, что Для консолидации по категории на одном листе leelera ячейкам Цикл по достаточно долгим. Name, Car, Number"chumich цель главная какая?! и низ затем = False 'отключаем объектно-ориентированных средах не всегда отличается.End If избежания ошибок, если"1. При выборезначения левого столбца данные на них диапазон данных на с помощью команды: колонке содержащей объединенные chumich а не "City,: Кликните здесь дляКак пытались сделать, на 2 строки обновление экрана для знакома, поэтому мой Заранее благодарю! NEXT_: в книгах есть только одной ячейкилибо оба флажка расположены одинаково. каждом из исходных "Консолидация". Например, если The_Prist, ячейки : Вот держите file2 ID, Name, Car, просмотра всего текста что конкретно не выше скорости 'вызываем диалог алгоритм тривиален иKuklPNext wsSh иные коды данные будут собраны одновременно. На конечном листе щелкните листов должен иметь есть отдельный лист уже пробовала, у Tumatayev с исправленным макросом. Number" Option Base 1 получается?l = rr.Rows.Count выбора файлов для в нем есть : ?If bPolyBooks ThenWith Application со всех листов Выделите на каждом листе

 верхнюю левую ячейку формат списка без расходов для каждого меня не срабатывает, как видите, я Работает около 4chumich Sub copyUnion() Dim

OLEGOFFWith Sheets(«Лист2») импорта FilesToOpen = элемент «ущербности» -migo wbAct.Close FalselCalc = .Calculation начиная с этой
нужные данные. Не области, в которой пустых строк и регионального представительства, с The_Prist не один на минут. Сделать, чтобы : Сначала действительно должны firstBook As Workbook :

rr.Copy .Range(«B» & Application.GetOpenFilename _ (FileFilter:=»All
для подсчета строк: Спасибо, не зналNext li.ScreenUpdating = False: ячейки. » &
забудьте включить в
требуется разместить консолидированные
столбцов. Кроме того, помощью консолидации можно : интересно...Как ни странно форуме Общими усилиями работал еще быстрее
происходить копирование и Dim secondBook As
Yoooo
.Rows.Count).End(IIf(Len(.Range(«B» & .Rows.Count)),
files (*.*), *.*»,
на листах он
про эту функцию.
With Application

excelworld.ru

объединение данных с нескольких листов книги на один (Макросы Sub)

 .EnableEvents = False: _
них ранее выбранные данные. категории должны быть
создать на базе — но только работает 40 секунд.
не смогу И вставка столбца ID, Workbook Dim thirdBook, макросом в VBA xlDown, xlUp)).Offset(1) _ MultiSelect:=True, Title:=»Files требует наличия столба Но к сожалению.ScreenUpdating = True: .Calculation = xlManualvbCrLf & «2. данные из верхней
Примечание: названы одинаково. Например, этих данных корпоративный у Вас неchumich
обратите внимание, что

 чтобы скопировать форматирование As Workbook Set

 можно так..Range(«A» & .Rows.Count).End(IIf(Len(.Range(«A» to Merge») If «без дыр». это, похоже, не .EnableEvents = True:
End With При выделении нескольких строки или левого Убедитесь, что справа и
если один из лист расходов. Такой срабатывает. Скорее всего: chumich, shanemac51 в файле 1 ячеек, потом же
secondBook = ActiveWorkbookDoktor1962 & .Rows.Count)), xlDown, TypeName(FilesToOpen) = «Boolean»В Вашем примере

 то что нужно. .Calculation = lCalc
'создаем новый лист ячеек данные будут столбца. Затем нажмите снизу достаточно свободных столбцов называется лист может содержать что-то не так Спасибо огромное! Выручили далеко не все идет цикл, который Set firstBook =: А надстройка консолидация xlUp)).Offset(1).Resize(l) = sh.Name Then MsgBox "Не я добавила столб Если строить сводную End With в книге для собраны только с

 кнопку ячеек для консолидированныхСред. итоговые и средние thumb делаете, вот и
от большой работы, ID из второго. заменяет значения скопированного Workbooks.Open(«D:\file1.xlsx») Set thirdBook данных не подходит?End With выбрано ни одного
А в таблицах таблицу таким образом,End Sub сбора указанного диапазона всехДобавить
данных.

, а другой — данные по продажам, не срабатывает. и я свободен))) Поэтому в таблице столбца ID на = Workbooks.Open(«D:\file3.xlsx») DimAleksSid

End If файла!» Exit Sub smile

 на всех листах. то она получается_Boroda_

Set wsDataSheet = листов.», Type:=8)
.
На вкладкеСреднее текущим уровням запасовZ и как раз третьего файла есть значения столбца City. mass(15, 2) For: В файле 2Next End If ‘проходимflorenus
вертикально — ориентированной.: А что вы ActiveWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))’для указания диапазона

Путь к файлу вводитсяДанные, консолидация не просуммирует и наиболее популярным: Странно, однако -

 можно изучить VB) одинаковые значения в

 У вас только i = 1 варианта, первый консолидацией,End Sub по всем выбранным:В «списке полей у автора макроса
’если нужно сделать без диалогового окна: в полев разделе эти столбцы. продуктам в рамках

Юрий М подскажите оптимальный источник

 1 и 2 заголовок не заменяется

 To 15 For второй формулами, ноmaxzeev файлам x =KuklP для добавления в не спросите? Или сбор данных на’Set iBeginRange =Все ссылкиСервисОткройте каждый из исходных всей организации.: Пора уже показать знаний?) столбце. или все значения j = 1
если у вас: Вопрос снят, оказалось 1 While x, не могли бы отчет» сводной таблицы

excelworld.ru

Сборка листов из разных книг в одну

забыли где взяли? новый лист книги Range(«A1:A10») ‘диапазон указывается.нажмите кнопку листов.Тип консолидации следует выбирать свой файл. Если

Tumatayevchumich остаются из ID? To 2 mass(i, по факту листов все очень простоПосле этого можно вернуться Вы расшифровать макрос? нет возможности выбрать Напомню — его с кодом нужный

После добавления данных изКонсолидацияНа конечном листе щелкните с учетом того, данные секретные -, тут уж для: у вас небольшиеTumatayev j) = firstBook.Sheets(1).Cells(i, в несколько раз и красиво, с в Excel иKuklP отдельные столбцы исходного зовут Дмитрий Щербаков’Set wsDataSheet =’Если диапазон не всех исходных листов. верхнюю левую ячейку

как выглядят объединяемые можете мне на каждого свой Смотрите значения кодового слова: Только заголовок меняется j) Next Next больше чем в помощью панели PLEX! запустить созданный макрос: Он не зашифрован. диапазона данных. (The_Prist) ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) выбран — завершаем и книг нажмитеВыберите в раскрывающемся списке области, в которой

planetaexcel.ru

Объединение данных с разных листов на один лист.

 листы. Если данные почту сбросить. Почта и выбирайте, чтоя применила бы а значение остается secondBook.Sheets(1).Activate secondBook.Sheets(1).Range(«A1:D19»).Copy thirdBook.Sheets(1).Activate примере,то формулами не Спасибо всем огромное! через менюflorenusА мне нужнаsboy

’цикл по книгам процедуру кнопкуфункцию требуется разместить консолидированные
на листах расположены в профиле.
вам подойдет: Учебники, массив от ID thirdBook.Sheets(1).Range(«B1»).Select ActiveSheet.Paste thirdBook.Sheets(1).Range(«B1:B19»).Copy вариант, читайте тогда
nervСервис — Макрос -: возможность в поле: макросы Дмитрия гуляют

For li =If iBeginRange IsОК, которую требуется использовать

данные.

единообразно (названия строк
lavAzza справочники, самоучителиSub copyUnion() Dimchumich
thirdBook.Sheets(1).Range(«A1»).Select ActiveSheet.Paste For про консолидацию, это
: Я так понял, Макросы (Tools -

KuklP «название строк» указывать
по сети, поэтому LBound(avFiles) To UBound(avFiles) Nothing Then Exit. для консолидации данных.Примечание: и столбцов могут: Помогите, пожалуйста, решить
leelera
firstBook As Workbook
: На первый взгляд, i1 = 1 средство Excel идеально
что там еще Macro — Macros), извините не правильно
несколько названий столбцов

не факт, что
If bPolyBooks Then
Sub

Примечание:Выделите на каждом листе Убедитесь, что справа и при этом различаться), ту же задачу: В файле более

 Dim secondBook As всё правильно написано. To 20 For подходит под вашу помимо копирования суммировать

или нажав выразился. Вы не исходной таблицы. макрос ТС взялSet wbAct =’Указываем имя листа Любые названия, не совпадающие нужные данные и снизу достаточно свободных воспользуйтесь консолидацией по — объединить листы 50 листов, в Workbook Dim ws1 Возможно у вас k1 = 1 структуру таблиц и надоALT+F8 могли бы расписатьKuklP с его сайта. Workbooks.Open(Filename:=avFiles(li))’Допустимо указывать в с названиями в нажмите кнопку

ячеек для консолидированных расположению. Если же (или файлы, что

 каждом листе шапка, As Worksheet Dim какое-то другое форматирование To 15 If задачу. В файле
maxzeev. Отобразится диалоговое окно какая команда что

: Может так(жмем кнопку)? Вот кусок, который
Else имени листа символы других исходных областях,Добавить данных.

на листах для

planetaexcel.ru

Объединение таблицы со всех листов в одну

 еще лучше) в таблица и есть ws2 As Worksheet ячеек в таблице
thirdBook.Sheets(1).Cells(i1, 1) = в , создан: Нет суммировать не открытия файла, где выполняет.200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub www() «забыли» скопироватьSet wbAct =

 подставки ? и могут привести к.
На вкладке соответствующих категорий используются один. Формат данных

 объединенные ячейки. Dim i, i1, первой книги. Поэтому mass(k1, 1) Then с помощью консолидации.

 нужно. Нужно было необходимо указать одинЕсли не трудно

Dim ws As’————————————————————————————— ThisWorkbook *. появлению в консолидированныхПуть к файлу вводитсяДанные одинаковые названия строк на всех листахМне нужно объединить j, j2, s1, макрос их перебирает, Cells(i1, 1) =Doktor1962 выделить все листы, или несколько (удерживая конечно. Мне нужно,

 Worksheet, l&’ Module: mConsolidatedEnd If’Если указать только данных отдельных строк в полев группе и столбцов (данные одинаковый. Листов больше все листы в spath, dt1 dt1 сравнивает со второй

CyberForum.ru

Как объединять данные с нескольких листов в один (Формулы/Formulas)

 mass(k1, 2) Next: Ув. AleksSid раз далее выделить данные CTRL или SHIFT) чтобы листы собиралисьWith Sheets(«Svod»)’ DateTime: 02.02.2010oAwb = wbAct.Name * то данные
или столбцов.Все ссылкиРабота с данными при этом могут 50. один. Искала и

 = Timer spath и воспринимает как Next End Sub уж вам нравится на всех листах файлов, листы из не со второй.UsedRange.Offset(1).ClearContents

 17:06’цикл по листам будут собираться соMARINKA.нажмите кнопку
быть расположены по-разному),

Тему прочитал, готовые здесь, и вообще

excelworld.ru

Объединить листы excel в один

 = Excel.ActiveWorkbook.Path & разные. Поэтому иВо вложении работающий расшифровывать мои ответы
(Ctrl+А), при этом которых надо добавить строки а сFor Each ws’ Author: The_Prist(Щербаков
For Each wsSh всех листов

: Для объединения несколькихПосле добавления данных изКонсолидация используйте консолидацию по скрипты не помогли, в интернете ответы «» Set secondBook не меняет. Вы пример. Файлы file1 (он над вашим, выделятся только данные к текущей книге. первой. In Worksheets Дмитрий) In wbAct.SheetssSheetName = InputBox(«Введите листов в один всех исходных листов. категории. подправить — знаний и пока что = ActiveWorkbook Set можете выложить сделанные и file3 поместите если заметите сообщением), на всех листахmaxzeev_Boroda_If Not ws.Name’ Purpose: http://www.excel-vba.ru/chto-umeet-excel/kak-sobrat-dannye-s-neskolkix-listov-ili-knig/
If wsSh.Name Like имя листа, с часто пользуюсь макросом, и книг нажмитеВыберите в раскрывающемся спискеБолее новые версии в ВБ нет. ничего не подошло. firstBook = Workbooks.Open(spath файлы в архиве?

 в корень диска могу кроме приведенного без пустых строк.
: Подскажите пожалуйста, как: .Offset(1) сотрите = «Svod» Then’Процедура сбора данных sSheetName Then которого собирать данные(если очень выручает. кнопку
функцию Office 2011 г.хм. этот скриптП.с. Плекс не & «file1.xlsx») Set Или хотя бы D или пропишите вами способа формулами Затем в панели объединить таблицы наAliyal = .Cells.Find(«*», с нескольких листов/книг’Если имя листа не указан, тоНо не понимаю,ОК, которую требуется использовать при сборке из подходит. Макросы пишу ws1 = firstBook.Sheets(1) скопировать в один новые пути к посоветовать Вам посчитать FLEX выбрать объединить 800 листах (имя: Здр-те [a1], xlFormulas, 1,’————————————————————————————— совпадает с именем данные собираются со что нужно изменить,. для консолидации данных.

Для консолидации по расположению файлов, а не на дилетантском уровне, Set ws2 = файл строчек по ним в коде. на пальцах. А данные на разных листов — даты)Aliya 1, 2).Row +InExSu листа, в который всех листов)», «Параметр»)

 чтоб объединялись толькоДля консолидации по категорииУстановите флажки в группе диапазон данных на

 из листов сработал. так что прошу secondBook.Sheets(1) Dim mass(200000) пять первых двух Макрос находится в расшифровывать написанное другими листах в одном на один лист,: у меня вопрос. 1: Привет! собираем данные’Если имя листа конкретные листы. диапазон данных наИспользовать в качестве имен каждом из исходныхспасибо, вопрос решен. отвечать максимально развернуто. As Long For столбцов всех трех file2 и запускается некрасиво листе, переключаем кнопку

 чтобы таблицы либо Мне понравился оченьws.UsedRange.Offset(1).Copy .Range(«a» &
Для Вашего случая,’и сбор идет не указано -Есть книги с
каждом из исходных, указывающие, где в листов должен иметь

yozhikЮрий М

 i = 2 таблиц. Только чтобы кнопкой.tw333k на выделенные данные располагались одна под ваш макрос. У l) похоже, можно оставить только с активной данные будут собраны листами 01,02….31, но листов должен иметь исходных диапазонах находятся формат списка без: как вариант под: В «Приемы» заглядывали? To 200000 j копии были точноTumatayev: Есть таблице в на каждом листе. другой, либо в меня имеется файлEnd If листы с длиной книги — то

 со вех листов так же там формат списка без названия: пустых строк и Ваш пример Subleelera = ws1.Cells(i, 1) такие же как: У меня одна google docs к ВСЕ! Как бонус одну общую таблицу? с несколькими страницами.Next имени листа =

 переходим к следующемуIf sSheetName = есть листы 01.,
пустых строк иподписи верхней строки
столбцов. sbor() Dim sh: If j > оригиналы в ваших проблема( которой доступ только я еще получил Таблицы на листах В каждом листеEnd With 2. листу «» Then sSheetName 02., 03!, 04итого столбцов. Кроме того,,Откройте каждый из исходных As Worksheet Worksheets.Add.NameЮрий М, 0 Then mass(j) файлах.Где я допустил на чтение. В автоматическое проставления дат, идентичные. примерно 5000 строк.End SubПопробуйте:If wsSh.Name = = «*» и тп. категории должны бытьзначения левого столбца листов и убедитесь = «Итог» lrзаглядывала, там есть = ws1.Cells(i, 2)Tumatayev ошибку? я только таблице имеется 12 поскольку листы уHugo И когда яmigoмежду строк wsDataSheet.Name And bPolyBooksOn Error GoTo

Что поменять в названы одинаково. Например,либо оба флажка в том, что = 1 For ответ по теме Else Debug.Print i,: Все варианты форматов размер таблиц поменял! листов которые имеют меня имели название
: Такие вопросы без применяю ваш макрос,: Да, то чтоFor Each wsSh In = False Then 0
 макросе, чтобы была если один из одновременно. данные на них Each sh In

 «объединить листы из ws1.Cells(i, 1) End
перепробовал, не получается( а он мне одинаковую структуру (столбцы дат, так что примера данных в то он очень надо! я именно

 wbAct.Sheets GoTo NEXT_’Запрос — вставлять возможность объединения только столбцов называетсяВыделите на каждом листе

CyberForum.ru

Объединение нескольких листов в один.

 расположены одинаково. ThisWorkbook.Worksheets If sh.Name разных книг», а If Next ForЕще сам добавил копирует не с
совпадают). не пришлось даже файле не решаются долго думает. Вопрос: так и хотелIf wsSh.Name LikeWith wsSh
на результирующий лист нескольких листов: например,Сред. нужные данные. НеНа конечном листе щелкните

 <> «Итог» Then у меня из

 i1 = 2 доп. функцию «если первый таблицы городаНеобходимо чтобы в редактировать таблицу. :) Почему так. Можетспасибо!! sSheetName Then

Select Case iBeginRange.Count все данные
только 01+02+03?

, а другой — забудьте включить в верхнюю левую ячейку sh.UsedRange.Copy Range(«A» &
одной книги. To 31673 j2 совпадении нет то
а со второй сторонний документ тянулись
Может эта информацияНе надо 800, быть я неправильноЯ правильно понял,вставить строкуCase 1 ‘собираем’или только значения
MARINKAСреднее них ранее выбранные области, в которой
lr) lr =Z = Val(«0» & выводит Empty» ID

 данные со всех окажется для кого хватит 3-х. применяю макрос или

 чтоб сделать такойКодIf Len(wsSh.Name) = данные начиная с ячеек (без формул:

, консолидация не просуммирует данные из верхней требуется разместить консолидированные Cells(Rows.Count, 1).End(xlUp).Row +: Сегодня, однако - ws2.Cells(i1, 1)) Ifмакрос и безOption Base 1

 12 листов, но то полезной.

Алгоритм примерно такой это зависит от же макрос в 2 Then указанной ячейки и и форматов)Option Explicit

 эти столбцы. строки или левого данные. 1 End If… j2 > 1 этой функции не Sub copyUnion() Dim появлялись они наHugo
— цикл по количества данных в моем файле, состоящеми
до конца данных
bPasteValues = (MsgBox(«ВставлятьSub Consolidated_Range_of_Books_and_Sheets()Откройте каждый из исходных
столбца.

Примечание: Next End Subleelera Then s1 = наработал firstBook As Workbook одном листе: В общем код всем листам, определяем файле. Помогите пож. из 9 страниц,между строкlLastrow = .Cells(1, только значения?», vbQuestionDim iBeginRange As

planetaexcel.ru

листов.

Источник

Имеется книга из 50 листов.
Везде списки. Названия полей везде одинаковые. В левом сотлбце уникальный ключ-идентификатор.
Есть ли возможность скопировать все эти данные в одну базу?
Пробовала консолидацию, но мы с ней не дружим
Выделять и копировать диапазоны — не вариант, т.к. листов очень много.
Данные все текстовые

Скажи мне, кудесник, любимец ба’гов…

Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

Пойдет
Но интересно бы еще найти штатный вариант.
Если кто знает — расскажите

Скажи мне, кудесник, любимец ба’гов…

Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…
www.excel-vba.ru
Просто СПАСИБО [+оказать+]
Считаешь СПАСИБО мало? Яндекс.Деньги: 41001332272872; WM: R298726502453

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

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

Здесь я выкладывал полноценный макрос, с выбором и книг, и листов.

Спасибо, попробую разобраться

И совсем простенький (в новую книгу)

Sub sborka() s_ = Sheets.Count Workbooks.Add ThisWorkbook.Sheets(1).Range("1:1").Copy ActiveWorkbook.Sheets(1).Range("a1") For i = 1 To s_ r_ = ActiveWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp).Row + 1 ThisWorkbook.Sheets(i).Range("a1").CurrentRegion.Offset(1).Copy ActiveWorkbook.Sheets(1).Range("a" & r_) Next End Sub

Скажи мне, кудесник, любимец ба’гов…

Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

Огромное спасибо _Boroda_

А есть ли какое-то средство для самого обычного копирования листов одной книги на новый лист сверху вниз? Не нужна абсолютно никакая консолидация цифр, их нет, заголовки можно считать обычными ячейками и никак не учитывать.
Количество колонок на листах разное, нужно скопировать всё.
Заранее спасибо!

Цитата: Selma от 30.12.2010, 11:04
А есть ли какое-то средство для самого обычного копирования листов одной книги на новый лист сверху вниз?

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

Всякое решение плодит новые проблемы…

Макрос может быть, например, таким:

Sub tt() s_ = Sheets.Count Sheets.Add After:=Sheets(s_) For i = 1 To s_ r_ = Sheets(i).Cells.SpecialCells(xlLastCell).Row Sheets(i).Range("A1", Sheets(i).Cells.SpecialCells(xlLastCell)).Copy Sheets(s_ + 1).Range("a" & n_ + 1) n_ = n_ + r_ Next End Sub

Скажи мне, кудесник, любимец ба’гов…

Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

задача похожа, но немного все же иная. интересует макрос который бы автоматически копировал (добавлял) строки из разных листов по мере их заполнения на главный (или сводный лист), т.е. при заполнении какой-либо строки на каком-либо листе данные по порядочку добавлялись на сводный лист. это возможно? :-[

то что надо!

только надо применить для тех книг в названиях которых есть символ «$»

Также желательно 3 пустые строки между данними книг.

‘макрос объединения нескольких листов в один

Sub UnionPages() For L = 2 To Sheets.Count Sheets(L).Range("A1:Z" & Sheets(L).UsedRange.Rows.Count + 1).Copy Sheets(1).Range("A" & Sheets(1).UsedRange.Rows.Count) Next End Sub
Источник: http://hammga.ru/blogs?id=43

Источник

Сборка листов из разных книг в одну

Ссылки по теме

Excel VBA Course — From Beginner to Expert

Обьединение нескольких листов данных в один макросом

Консолидация данных из нескольких листов на одном.

Как скопировать несколько листов Excel в один.

1. Запустите мастер копирования листов.

1. Выберите листы и, при необходимости, диапазоны для объединения.

2. Выберите, каким образом произвести объединение.

Как объединить листы Excel с различным порядком столбцов.

Объединение определенных столбцов из нескольких листов.

Слияние листов в Excel с помощью кода VBA

Как объединить два листа Excel в один по ключевому столбцу

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

Объединение всех листов активной книги в один лист с помощью VBA

Объединение десятков листов или книг в один лист / книгу с помощью щелчков мышью

Демонстрация: объединение листов / книг в один лист или книгу

Объединение двух таблиц в одну и обновление по столбцу кликами

Демонстрация: объединение листов / книг в один лист или книгу

Скачать образец файла

Рекомендуемые инструменты для повышения производительности

Вкладка Office — просмотр, редактирование и управление документами с вкладками в Microsoft Office 2019 — 2003 и Office 365

Профессиональная надстройка для ускорения работы Excel 2019-2007, сокращающая часы работы до секунд

Объединение данных с нескольких листов

Консолидация по расположению

Консолидация по категории

Консолидация по расположению

Консолидация по категории

объединение определенных листов в один (Макросы/Sub)

объединение данных с нескольких листов книги на один (Макросы Sub)

Сборка листов из разных книг в одну

Объединение данных с разных листов на один лист.

Объединение таблицы со всех листов в одну

Как объединять данные с нескольких листов в один (Формулы/Formulas)

Объединить листы excel в один

Объединение нескольких листов в один.

А вот еще интересные статьи: