Option Explicit

Sub Макрос1()

Dim Gruppy As Integer

Dim List As Integer

Dim Stolbec As Integer

Dim i As Integer

Dim k As Integer

Dim Name As String

Dim Name2 As String

' определяем имя нашей книги. если оно известно заранее, то можно вбить сразу и навсегда

Name = ActiveWorkbook.Name

' ищем сколько всего групп. в пределах 100 строк d столбе А должен быть только номер листа для группы

' А1 номер листа В1 номер столбца - группа 1

' Аn номер листа Вn номер столбца - группа n

' если An = пусто , то групп больше нет

For i = 1 To 100

    If Cells(i, 1).Value <> "" Then

        Gruppy = i

    End If

Next i

' как работать с закрытой книгой я не знаю, поэтому открываем удаленную книгу только для чтения

Name2 = "Архив.xls"

Workbooks.Open "c:\temp\" & Name2, ReadOnly:=True

' передаем фокус на нашу книгу

Workbooks(Name).Activate

For i = 1 To Gruppy

    List = Cells(i, 1)

    Stolbec = Cells(i, 2)

    k = 1

    ' перебрасываем данные, пока они есть в столбце другой книги

    While Workbooks(Name2).Sheets(List).Cells(k, i).Value <> ""

    ' даные из столбца другой книги пишем к себе в столбец

    ' для 1 группы = столбец №3 (С), для второй = №4 (D)...

    Cells(k, i + 2).Value = Workbooks(Name2).Sheets(List).Cells(k, Stolbec).Value

    k = k + 1

    Wend

Next i

' закрываем другую книгу

Workbooks(Name2).Close

End Sub