Поиск по сайту:

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


Объединение данных из нескольких листов Excel в одной книге — настоящая проблема… пока вы не используете макросы VBA.

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

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

Следуя этому руководству, вы создадите свой собственный макрос VBA в Excel и эффективно объедините несколько листов в один.

Объединение нескольких листов Excel в одной книге

Для этой задачи данные сохраняются на следующих листах:

  • Лист1
  • Лист2
  • Лист3

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

Предварительные условия для запуска кода

Существуют некоторые предварительные условия для запуска кода VBA, перечисленные ниже.

Вам необходимо сохранить код макроса в новом файле Excel. Сохраните эту книгу с расширением .xlsm . Вы можете сохранить книгу макросов VBA под любым именем.

Откройте новый файл Excel; нажмите Alt + F11 на клавиатуре, чтобы открыть редактор Excel VBA. После открытия редактора добавьте новый модуль кода, нажав вкладку Вставка вверху. Выберите Модуль, чтобы вставить новый модуль; здесь вы будете вводить код макроса VBA, приведенный ниже.

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

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

Консолидированные данные будут доступны на листе под названием Консолидированные.

Запуск кода VBA

Пришло время запустить только что сохраненный код макроса. Скопируйте и вставьте этот код в модуль редактора VBA:

Sub consolidate_shts()
'declare the various variables used within the code and the vba data types
Dim sht As Worksheet, sht1 As Worksheet, lastrow As Integer, lastrow1 As Integer
'disable screen flickering and alert pop-ups during the execution
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
'store the name of the primary workbook in the a macro variable. Replace Test.xlsx with the name of your primary workbook
Set wbk1 = Workbooks("Test.xlsx")
'activate the workbook before performing the function(s) on it
wbk1.Activate
'run a vba for loop to check if a sheet Consolidated already exists. If it exists, the for loop will delete it.
For Each sht In wbk1.Sheets
If sht.Name = "Consolidated" Then sht.Delete
Next sht
'Add a new sheet to store the newly consolidated data
Worksheets.Add.Name = "Consolidated"
'Add some headers to each individual column within the consolidated sheet
With Sheets("Consolidated")
.Range("a1").Value = "OrderDate"
.Range("b1").Value = "Region"
.Range("c1").Value = "Rep"
.Range("d1").Value = "Item"
.Range("e1").Value = "Units"
.Range("f1").Value = "UnitCost"
.Range("g1").Value = "Total"

End With
'The newly created sheet consolidated will hold the consolidated data from each individual sheet in the primary workbook

For i = 1 To wbk1.Worksheets.Count
If Sheets(i).Name <> "Consolidated" Then
'Capture the last populated row from the data sheets in the workbook
lastrow = Sheets(i).Range("a1").End(xlDown).Row
'Capture the last populated row in the Consolidated sheet
lastrow1 = wbk1.Sheets("Consolidated").Range("a1048576").End(xlUp).Row + 1

'Copy data from source sheet and paste it in the consolidated sheet
Sheets(i).Range("a2:g" & lastrow).Copy Destination:=Sheets("Consolidated").Range("a" & lastrow1)
End If
Next i
'Enable Excel VBA functions for future use
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

End Sub

Объяснение кода VBA

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

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

На следующем шаге вам необходимо определить имя книги, которая содержит все ваши данные. Замените Test.xlsx именем и расширением имени вашей книги. Обязательно заключайте имя в кавычки.

Активируйте основную книгу и удалите все существующие листы с именем Объединенная , чтобы удалить все ранее сохраненные данные. Код VBA переключается между каждым листом, и как только он встречает имя листа Consolidated, он удаляет его. Это делается с помощью оператора IF VBA, который проверяет логические условия и удаляет лист, как только условие выполняется.

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

Например: .Range("a1")="OrderDate" можно заменить на .Range("a1")="OrderNumber"

Затем цикл VBA FOR переключается по каждому листу, копирует его содержимое и вставляет его в Объединенный рабочий лист перед переходом к следующему листу в книге. Этот процесс повторяется до тех пор, пока все листы не будут скопированы.

Во время этого процесса все строки автоматически вычисляются и вставляются в сводный лист. Последняя заполненная строка вычисляется автоматически перед вставкой данных. Макрос является динамическим и может адаптироваться к различным строкам данных на каждом листе.

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

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

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

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

Статьи по данной тематике: