Перебор листов в книге Excel циклом For Each… Next с копированием данных из этих листов и вставкой в новый лист той же книги с помощью кода VBA.
Условие задачи по перебору листов
Есть книга Excel с неизвестным количеством рабочих листов с данными. Необходимо выполнить следующие действия:
- открыть книгу;
- создать новый лист;
- запустить цикл перебора листов;
- скопировать данные из столбца «B» каждого листа и вставить в новый лист;
- данные из очередного листа вставлять в следующий столбец нового листа, а в верхнюю ячейку столбца записывать имя листа, из которого данные скопированы.
Перебор листов в книге Excel
Для открытия книги (получения полного имени) будем использовать диалоговое окно выбора файлов GetOpenFilename, а для перебора листов — цикл For Each… Next.
Пример кода для перебора листов в книге Excel циклом For Each… Next с частичным копированием данных на отдельный лист:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
Sub CopyDataFromAllSheets() Dim wb As Workbook, newws As Worksheet, ws As Worksheet, n As Long n = 1 'Выбираем, открываем нужную книгу и присваиваем ссылку на нее переменной wb Set wb = Workbooks.Open(Application.GetOpenFilename("Файлы Excel,*.xls*", , "Выбор файла")) 'Создаем в открытой книге новый лист и присваиваем ссылку на него переменной newws Set newws = wb.Worksheets.Add With newws 'Присваиваем имя новому листу: "Отчет от dd.mm.yyyy" .Name = "Отчет от " & Date 'Запускаем цикл, перебирающий листы For Each ws In wb.Worksheets 'Проверяем, что имя текущего листа не равно имени нового листа "Отчет..." If ws.Name <> newws.Name Then 'Копируем столбец "B" текущего листа на лист "Отчет..." в столбец n ws.Columns("B").Copy Destination:=.Columns(n) 'Добавляем ячейку cверху столбца n .Cells(1, n).Insert Shift:=xlShiftDown 'Записываем в добавленную ячейку имя текущего листа .Cells(1, n) = ws.Name 'Задаем тексту в добавленной ячейке полужирное начертание .Cells(1, n).Font.Bold = True n = n + 1 End If Next End With End Sub |
При использовании метода копирования диапазонов, на новый лист будут перенесены не только значения ячеек, но и их форматы. Если необходимо перенести только значения, можно, в качестве промежуточного звена, использовать массив.