ご説明したい内容の準備が出来ました。下図①の様にサンプルデータを、下図②、③の様にプログラムファイルをフォルダーに入れて下さい。
ダウンロードリンク先 → hiro-yukioのサイト 次に進みます。
図①
図②
図③
図②および図③のプログラムを実行してみて下さい。イミディエイトウィンドウに図②、図③の様に表示が出ます。
「対象ブックの表題名_1( 1 )」～「対象ブックの表題名_1( 最終列番号 )」の値（表題項目）と
氏名列、住所1列、住所3列の値（列番号）がそれぞれ表示されています。想像しやすいように図②、図③の下の部分に赤枠と緑枠の表を入れてあります。
図②は表題列が１３列あり、氏名列は３列目、住所1列は７列目、住所3列は９列目です。
図③は表題列が８列あり、氏名列は２列目、住所1列は６列目、住所3列は８列目です。
いずれも配列変数の番号（要素番号）とシート上の列番号が一致する様にプログラムを作成しています。「170824_サンプルデータ１●.xlsx」「170729_サンプルデータ１.xlsx」を開いて確認してみて下さい。
具体的にご説明します。プログラムファイルのModule1を見て下さい。どちらのプログラムファイルのModule1も同じプログラムです。
配列変数について、
Dim 対象ブックの表題名_1( ) As String
ここで一旦配列変数の宣言をします。最大使用番号（要素数）は後で宣言します。この後、
対象ブックの最終列_1 =
.Cells(1, Columns.Count).End(xlToLeft).Column
の式で表題列の最大値を把握（取得）します。
次の式で、配列変数の最大使用番号（要素数）を宣言します。
ReDim 対象ブックの表題名_1(対象ブックの最終列_1)
次のFor ～ Next文で 表題の項目の全てを 配列変数に代入します。この際、シートの列番号と配列変数の番号（要素番号）が一致する様にしてあります。 ここがポイント！↑↑
For カウンター変数 = 1 To 対象ブックの最終列_1
対象ブックの表題名_1(カウンター変数) = .Cells(1, カウンター変数)
Next カウンター変数
この後、列番号が必要な表題の列番号をあらかじめ変数宣言していた変数に代入します。（Select Case ～ End Select文を使用）
For カウンター変数 = 1 To 対象ブックの最終列_1
Select Case True
Case 対象ブックの表題名_1(カウンター変数) = "氏名"
氏名列 = カウンター変数
Case 対象ブックの表題名_1(カウンター変数) = "住所１"
住所1列 = カウンター変数
Case 対象ブックの表題名_1(カウンター変数) = "住所３"
住所3列 = カウンター変数
End Select
Next カウンター変数
これで、コピーすべき最終行をデータが必ず入っている「氏名」で把握（取得）出来ます。
対象ブックの最終行 = .Cells(Rows.Count, 氏名列).End(xlUp).Row
また、住所の書式を「文字型」に必ず変更することが出来ます。（住所1、住所2、住所3が隣り合っていることが前提）
.Range(.Cells(1, 住所1列), .Cells(対象ブックの最終行, 住所3列)).NumberFormatLocal = "@"
この様に、配列変数 と Select Case文を使用することで、表題項目の並び方が変化しても 表題項目数が増減しても プログラムを修正することなく、 複数ファイルを結合する（ファイルを結合させる）プログラムファイルを汎用的に使うことが出来ます。
余談ですが、私は最初、配列変数 と Select Case文を使用せずに、住所1列 = WorksheetFunction.Match("住所1", Range(Cells(1, 1), Cells(1, 最終列)), 0)の様に、WorksheetFunction.Matchを使用して作成していました。
しかし、列数を取得したい表題項目が増えると、その都度この式を追加する必要があり、また、プログラムの可読性が悪くなることもあって、新たに列数取得が必要な表題項目が出てきた場合にはSelect Case文の中に式を追加する方法を思いつきました。
次回は、複数ファイルの結合における、ファイル数カウントとデータ数のカウントをご説明予定です。お楽しみに。
読者の方へ。過去の記事をご覧になられていて、「画像」が上手く表示されない場合は、「hiro-yukioのサイト」の「過去のブログ記事」フォルダーにPDFで保存しましたのでこちらでご覧下さい。