以下のコードを標準モジュールに貼り付けて、定数 BASE_DIR に三つのシートがあるディレクトリ名をフルパスで設定してください。
そして、merge_files サブルーチンを実行してください。
開いているシートに三つのファイルからデータを取り込みます。
Const COLUMN_PATTERN = "氏名|年齢|性別|血液型|備考" Const DIV_PATTERN = "関東|関西|東北" Const BASE_DIR = "D:\foo\bar" Sub merge_a_file(filename) Set this_book = ActiveWorkbook Set this_sheet = ActiveSheet Set ref_book = Workbooks.Open(BASE_DIR & "\" & filename) Set ref_sheet = ref_book.Sheets(1) ' ひとつめのシート ' 区分をファイル名から抽出 Set re = CreateObject("VBScript.RegExp") re.Pattern = DIV_PATTERN Set mat = re.Execute(filename) If mat.Count = 0 Then Exit Sub End If div = mat(0) ' 取り込む列位置を求める Set col_map = CreateObject("Scripting.Dictionary") re.Pattern = "^(" & COLUMN_PATTERN & ")$" last_col = ref_sheet.Cells(1, Columns.Count).End(xlToLeft).Column For c = 1 To last_col Set mat = re.Execute(ref_sheet.Cells(1, c).Value) If mat.Count > 0 Then col_map.Add mat(0).submatches(0), c End If Next Set re = Nothing this_book.Activate this_sheet.Activate ' 参照シートの値を取り込む last_row = ref_sheet.Cells(Rows.Count, 1).End(xlUp).Row dest_row = Cells(Rows.Count, 1).End(xlUp).Row + 1 cols = Split(COLUMN_PATTERN, "|") For r = 2 To last_row Cells(dest_row, 1).Value = div For i = 0 To UBound(cols) Key = cols(i) If col_map.exists(Key) Then c = col_map.Item(Key) Cells(dest_row, i + 2).Value = ref_sheet.Cells(r, c).Value End If Next dest_row = dest_row + 1 DoEvents Next Set col_map = Nothing ref_book.Close Set ref_book = Nothing End Sub Sub merge_files() ' 取り込み対象のファイル名 Dim filelist(3) filelist(1) = "data関東.xlsx" filelist(2) = "関西リスト.xlsx" filelist(3) = "20160422 東北一覧.xlsx" ' 見出し行 Cells(1, 1).Value = "区分" cols = Split(COLUMN_PATTERN, "|") For i = 0 To UBound(cols) Cells(1, i + 2).Value = cols(i) Next ' クリア Range(Cells(2, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, UBound(cols))).Clear ' ファイルから取り込む For i = 1 To UBound(filelist) Call merge_a_file(filelist(i)) Next ' ファイルを保存する new_name = "data_" & Format(Now, "yyyymmddHHMMSS") & ".xlsm" ActiveWorkbook.SaveAs new_name, xlOpenXMLWorkbookMacroEnabled End Sub
質問では条件に挙げられていなくて、前提にしていること。
補足:備考の項目は”関東”が含まれるファイルにのみ存在する。”関西””東北”の場合、データ行をグレー色にて示します。
備考に限らず、取り込み先のタイトル行に定数 COLUMN_PATTERN で指定した項目がなければ、取り込み開始前にシートをクリアしているので、空白のままになります。
「データ行をグレー色にて示します」のところはよく分からないので、特に扱ってません。
なお、2 3 4 5 6 のようなデータが混入される
見出し行の項目の名前の一部が重複する場合に対応できるように、上記のコードを修正しました。