VBAマクロで離れた結合行に順次データを挿入

質問

マクロ初心者です。
本やサイトを参考に調べたのですが、どうしても自力ではできないので、お力をお貸しください。
特殊な書式を使わなければならないため、既存のexcelテンプレートでは対応できません。

同一フォルダ内での作業です。
各学級に委員会は２～３名、クラブは０～１０名が在籍しています。

＜データ＞
学級毎に別ファイルの名簿があります。
１行目が項目です。
２行目以下がデータで、出席番号順です。
１クラス４０名以下です。
このデータファイルはエクセル初心者でも入力しやすいようにしておく必要があるため
あまり変更はできません。
委員会およびクラブ名は、プルダウンで入力されています。

　　　A　　　　　B　　　　C　　　D　　　　E　　　　F
１　出席番号　学年学級　氏名　ふりがな　委員会　クラブ名
２
３
４
・
・
・

＜作業１＞
＜データ＞から委員会ごと、クラブごとの名簿を作りたいです。
○○委員会、△△クラブをプルダウンで選択すると
３行目以下に名簿が学級毎出席番号順で並ぶようにしたいです。
委員会とクラブは、別シートあるいは別ファイルにしたいです。

　　　　　　　　　A　　　　　　　　　B　　　　　　　　　C
１（項目行）　平成○年度　（委員会／クラブ名選択）　委員会／クラブ
２（項目行）　学年学級　　　　　　　　氏名　　　　　　　ふりがな
３　　　　　　学年学級１　　　　　　　氏名１　　　　　　ふりがな１
４　　　　　　学年学級１　　　　　　　氏名２　　　　　　ふりがな２
５　　　　　　学年学級２　　　　　　　氏名３　　　　　　ふりがな３
６　　　　　　学年学級２　　　　　　　氏名４　　　　　　ふりがな４
７　　　　　　学年学級２　　　　　　　氏名５　　　　　　ふりがな５
８　　　　　　学年学級３　　　　　　　氏名６　　　　　　ふりがな６
９　　　　　　学年学級３　　　　　　　氏名７　　　　　　ふりがな７
10　　　　　　学年学級３　　　　　　　氏名８　　　　　　ふりがな８
11　　　　　　学年学級３　　　　　　　氏名９　　　　　　ふりがな９
12　　　　　　学年学級３　　　　　　　氏名10　　　　　　ふりがな10
・　　　　　　　　・　　　　　　　　　　・
・　　　　　　　　・　　　　　　　　　　・
・　　　　　　　　・　　　　　　　　　　・

＜作業２＞
＜データ＞もしくは＜作業１＞から
評価カードに、
学年学級と委員会／クラブ名をプルダウンで選択することで
下記のように氏名を挿入したいです。
氏名の横などに担当者が手書きで評価を記入していく書式です。
この書式は変更不可能です。

　　A　　　　　　　　　　　　　　　　　　
１　A空白　　BCDEFG結合（学年学級選択）　HI空白　　JKLMN結合（委員会／クラブ名選択）
２（空白行）
３　　　　　　　　　　　JKLMNOPQRSTUV結合（担当者名が自動挿入）
４（空白行）
５（空白行）
６（空白行）
７（空白行）
８（説明行）
９・10結合（項目行）
11・12結合　ABCDE結合（氏名１）
13（説明行）
14（説明行）
15（説明行）
16（空白行）
17・18結合（項目行）
19・20結合　ABCDE結合（氏名２）
21（説明行）
22（説明行）
23（説明行）
24（空白行）
25・26結合（項目行）
27・28結合　ABCDE結合（次の氏名）

以下、下記の欄に続くの氏名が順次挿入されるようにしたいです。
35・36結合　ABCDE結合
43・44結合　ABCDE結合
51・52結合　ABCDE結合
59・60結合　ABCDE結合
67・68結合　ABCDE結合
75・76結合　ABCDE結合
83・84結合　ABCDE結合

説明の足りないことがあればご教示ください。
どうぞよろしくおねがいいたします。

回答No.1

nazo-nazo

高度（VBAの精通者なら30分程度で仕上げるかもしれないが）な内容だと思います。
それに全ての回答は大変なので、質問をさらに分解して聞いた方がよろしいかも。

＜作業１＞のヒントだけですみません。

(1)委員会／クラブ名が選択されるセルはWorksheet_ChangeのTarget.Addressで認識する。

例：セルB1が変更された。
Private Sub Worksheet_Change(ByVal Target As Range)
If StrComp("$B$1", Target.Address, vbTextCompare) = 0 Then
msgbox "セルB1が変更された。"
End If
End Sub

(2)別ファイルのデータはWorkbooks.Openでそれぞれファイルを開きデータを取得する。