Решение задачи по копированию данных с одного листа на другой без использования и с использованием массивов. Вызов из кода VBA Excel других процедур.
Условие задачи по копированию данных
На одном листе расположен список повторяющихся городов с информацией о предприятиях общепита:
Исходная таблица задания №1
Необходимо данные по каждому городу перенести в одну строку на другом листе (таблица обрезана справа):
Часть результирующего списка задания №1
Решение копированием с листа на лист
Это решение значительно проще, чем с использованием массивов, но более медленное. При больших объемах информации обработка может длиться достаточно долго. Решение достигается путем присваивания значений ячеек из таблицы первого листа ячейкам второго листа.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Sub Resheniye1() Dim n1 As Long, n2 As Long, n3 As Long, n4 As Long, _ i1 As Long, gorod As Variant n1 = Sheets("Лист1").Cells(1, 1).CurrentRegion.Rows.Count For i1 = 1 To n1 With Sheets("Лист1") If gorod <> .Cells(i1, 1) Then gorod = .Cells(i1, 1) n2 = 1 n3 = n3 + 1 n4 = 1 Else n2 = 2 End If Do While .Cells(i1, n2) <> "" Sheets("Лист2").Cells(n3, n4) = .Cells(i1, n2) n4 = n4 + 1 n2 = n2 + 1 Loop End With Next End Sub |
Переменные:
- n1 – количество строк в исходной таблице;
- n2 – номер столбца текущей ячейки исходной таблицы, к которой обращается цикл;
- n3 – номер строки текущей ячейки на втором листе;
- n4 – номер столбца текущей ячейки на втором листе;
- i1 – счетчик цикла For… Next;
- gorod – переменная с наименованием города, предназначенная для контроля за сменой текущего города, который обрабатывается циклом.
Решение с использованием массивов
Циклы в массивах работают очень быстро, но решение с ними может быть сложнее, чем при простом присваивании значений одних ячеек другим. В этом примере для упрощения кода две процедуры записаны отдельно и в нужные моменты вызываются для исполнения.
|
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 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
'Объявление глобальных переменных 'в разделе Declarations Dim massiv1 As Variant, n2 As Long, _ n3 As Long, i1 As Long, txt1 As Variant 'Исполняемая процедура для решения 'задания вторым способом Sub Resheniye2() Dim n1 As Long, gorod As Variant With Sheets("Лист1").Cells(1, 1) massiv1 = .CurrentRegion n1 = .CurrentRegion.Rows.Count n2 = .CurrentRegion.Columns.Count End With n3 = 0 txt1 = "" For i1 = 1 To n1 If gorod <> massiv1(i1, 1) Then If txt1 <> "" Then Call Vstavka End If gorod = massiv1(i1, 1) txt1 = massiv1(i1, 1) Call Kopirovanie Else Call Kopirovanie End If If i1 = n1 Then Call Vstavka End If Next End Sub 'Копирование данных из массива в 'строковую переменную через разделитель Sub Kopirovanie() Dim i2 As Long For i2 = 2 To n2 If massiv1(i1, i2) <> Empty Then txt1 = txt1 & "|" & massiv1(i1, i2) End If Next End Sub 'Обработка данных из строковой 'переменной в дополнительных массивах и 'вставка очередной строки на второй лист Sub Vstavka() Dim n4 As Long, massiv2 As Variant, _ massiv3 As Variant, i3 As Long n3 = n3 + 1 massiv2 = Split(txt1, "|") n4 = UBound(massiv2) ReDim massiv3(0 To 0, 0 To n4) For i3 = 0 To n4 massiv3(0, i3) = massiv2(i3) Next Sheets("Лист2").Range(Cells(n3, 1), _ Cells(n3, n4 + 1)).Value = massiv3 End Sub |
Подпрограммы Kopirovanie и Vstavka используются в цикле For... Next процедуры Resheniye2 по два раза, поэтому их коды вынесены за пределы процедуры Resheniye2 и вызываются по мере необходимости.
Переменные:
- massiv1 – его элементам присваиваются значения ячеек исходной таблицы;
- massiv2 – одномерный массив, заполняемый данными из переменной txt1;
- massiv3 – двумерный массив, заполняемый данными из одномерного массива massiv2 и используемый для вставки очередной строки на второй лист;
- txt1 – сюда копируются через разделитель значения элементов массива massiv1, предназначенные для заполнения очередной строки на втором листе;
- n1 – количество строк в исходной таблице;
- n2 – количество столбцов в исходной таблице;
- n3 – номер текущей строки на втором листе;
- n4 – количество столбцов текущей строки на втором листе (соответствует количеству элементов массива massiv2);
- i1, i2, i3 – счетчики цикла For… Next;
- gorod – переменная с наименованием города, предназначенная для контроля за сменой текущего города, который обрабатывается циклом.
Переменные, использующиеся более чем в одной процедуре, объявлены как глобальные в разделе Declarations программного модуля.
Классно! Авторам — уважение! Толково, без соплей, со знанием дела.
Можете подсказать синтаксис такой проблемы: таблица 10 строк, 8 столбцов, если первая ячейка не пустая, скопировать на другой лист этой книги, а лучше — в отдельную книгу всю строку таблицы. Потом то же самое со строкой ниже, и так до последней. Потом пустые строки надо скрыть.
Это сложно?
Заранее спасибо за ответ.
Привет, Илья!
Для диапазона A1:H10:
Здравствуйте, Евгений! Огромное Вам спасибо! Потраченное время и желание помочь дорогого стОит, это большая редкость в наше время, поверьте. Ваш макрос — не совсем то, что мне нужно (например, новую книгу открывать не нужно, она уже есть, нужно просто на неё (и нужный лист) сослаться). Месторасположение ячеек в книгах не совпадает, строки тоже (27-я строка первой книги вставляется в 6-ю второй, т.е., например D27 в E6). И самое главное — вторая книга заполняется с нарастающим итогом. Т.е с первой книги берутся данные, вносятся во вторую, потом первая заполняется по новой, эти данные вносятся во вторую, но уже ниже и т.д.
Но, в любом случае, огромное спасибо, Вы дали направление, куда двигаться, буду думать ):
Добрый день, Евгений!
Существует способ автоматической передачи (копирования) данных из одного файла эксель в другой методом =…
А метод автоматической передачи (копирования) цвета имеется?
Подскажите пожалуйста!
Добрый день, Константин!
Мне такой метод не известен.
Спасибо за код и описание действий, очень помогло, делал по аналогии для своего проекта!!!