複数のExcelファイルのデータを、一つのBookにまとめたいときは、”パワークエリ”を使えば簡単です。
複数ファイルを一つのフォルダにまとめるだけ、コードなどを記述する必要もありません。下記の手順を参考にお試しください！

手順

1. 合体したい複数のExcelファイルを、1つのフォルダの中に入れる

2. 新規Excelを開く

3. タブ「データの取得」→「ファイルから」→「フォルダーから」をクリック。

4. １．のフォルダを選んで、OKをクリック

5. 結合→結合および読み込み　をクリック

6. 該当のシート名を選択して　OKをクリック

7. この新規Excel上に結合ファイルが作られる