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

Posted on: 2020年10月22日 2020年11月12日
Tags: cells-ranges-rows-and-columns-in-vba

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

Пример:

008

Здесь я получил некоторые данные с сервера, который возвращает данные в разные рабочие листы. Я добавил еще один лист и назвал его «Мастер». Другие названия листов значения не имеют.

Теперь запустите этот макрос.

Sub Merge_Sheets()

Dim startRow, startCol, lastRow, lastCol As Long

Dim headers As Range

'Set Master sheet for consolidation

Set mtr = Worksheets("Master")

Set wb = ThisWorkbook

'Get Headers

Set headers = Application.InputBox("Select the Headers", Type:=8)

'Copy Headers into master

headers.Copy mtr.Range("A1")

startRow = headers.Row + 1

startCol = headers.Column

Debug.Print startRow, startCol

'loop through all sheets

For Each ws In wb.Worksheets

'except the master sheet from looping

If ws.Name <> "Master" Then

ws.Activate

lastRow = Cells(Rows.Count, startCol).End(xlUp).Row

lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column

'get data from each worksheet and copy it into Master sheet

Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _

mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)

End If

Next ws

Worksheets("Master").Activate

End Sub

Как объединить листы с помощью этого макроса VBA?

Вставьте новый лист и назовите его «Мастер» в книге. Если хотите, переименуйте его позже.
Вставьте модуль в редактор VBA и скопируйте вышеуказанный код VBA.
Запустите макрос.
Вам будет предложено выбрать заголовки. Выберите заголовок и нажмите ОК.

И это сделано. Все листы объединяются в мастер.

0012

Как это работает?

Я предполагаю, что вы знакомы с основами создания объектов и переменных в VBA. В первой части мы создали объект и переменные, которые нам понадобятся в наших операциях.

Ну, большинство вещей, которые я объяснил, используя комментарии в коде vba.

Давайте посмотрим на основную часть этого кода vba.

For Each ws In wb.Worksheets

'except the master sheet from looping

If ws.Name <> "Master" Then

ws.Activate

lastRow = Cells(Rows.Count, startCol).End(xlUp).Row

lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column

'get data from each worksheet and copy it into Master sheet

Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _

mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)

End If

Next ws

В предыдущих статьях мы узнали, как перебирать листы и как получить последнюю строку и столбец с помощью vba.

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

Для каждого ws в wb.Worksheets Затем мы исключаем «главный» лист из цикла, поскольку мы будем консолидировать наши данные в этом листе.

Затем мы получаем номер последней строки и последнего столбца.

Теперь следующая строка очень важна. Мы выполнили несколько операций в одной строке.

Range (Cells (startRow, startCol), Cells (lastRow, lastCol)). Копировать _ mtr.Range («A» & mtr.Cells (Rows.Count, 1) .End (xlUp) .Row + 1)

Сначала мы формируем диапазон, используя startRow, startCol, lastRow и lastCol.

Range(Cells(startRow, startCol), Cells(lastRow, lastCol))

We have copied it using copy method of range.

Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy

We pasted it directly into first blank cell after last non blank cell in column A of master sheet (mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1).

Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _

mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)

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

Наконец, в конце макроса мы активируем мастер-лист, чтобы увидеть результат.

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

Скачать файл:

`link: /wp-content-uploads-2019-11-Consolidate_Merge-multiple-worksheets-into-one-master-sheet-using-VBA.xls [__ Объединить несколько рабочих листов в один мастер-лист с помощью VBA]

Статьи по теме:

Как пролистывать листы

как получить последнюю строку и столбец с помощью vba

link: / files-workbook-and-worksheets-in-vba-add-and-save-new-workbook-using-vba-in-microsoft-excel [Добавить и сохранить новую книгу с помощью VBA в Microsoft Excel 2016]

link: / menus-toolbars-status-bar-in-vba-display-a-message-on-the-statusbar-using-vba-in-microsoft-excel [Показать сообщение в строке состояния Excel VBA]

link: / general-themes-in-vba-turn-off-warning-messages-using-vba-in-microsoft-excel [Отключить предупреждающие сообщения с помощью VBA в Microsoft Excel 2016]