VBA Excel. Копирование данных с одного листа на другой

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

1. Условие задачи по копированию данных
2. Решение копированием с листа на лист
3. Решение с использованием массивов

Условие задачи по копированию данных

На одном листе расположен список повторяющихся городов с информацией о предприятиях общепита:

Необходимо данные по каждому городу перенести в одну строку на другом листе (таблица обрезана справа):

Решение копированием с листа на лист

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

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 – переменная с наименованием города, предназначенная для контроля за сменой текущего города, который обрабатывается циклом.

Решение с использованием массивов

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

'Объявление глобальных переменных
'в разделе 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 программного модуля.