複数のエクセルファイルを1つのブックにまとめる(シート別)マクロの解説!

複数のエクセルファイルを1つのブックにまとめる(シート別)マクロの解説!

※ 当サイトではアフィリエイト広告を利用しています

 

Excelで複数のブックやシートをひとつにまとめたいことって、時々ありますよね?

そこで今回は「複数のエクセルファイルを1つのブックにまとめる方法(シート別)」をExcelマクロで解説していきたいと思います。

 

 本記事でわかること

1.複数のエクセルファイルを1つのブックにまとめる(シート別)マクロの使い方

2.複数のエクセルファイルを1つのブックにまとめる(シート別)マクロの作り方

 

本記事で紹介するマクロを使うと、以下図のように複数のエクセルファイルを1つのブックにまとめる方法(シート別)ことができます。

 

■Before

複数点在しているExcelブックを・・・

 

■After

1つのブックにまとめられる!

 

複数のエクセルファイルを1つのブックにまとめる(シート別)マクロの導入手順

 

ではさっそく、「複数のエクセルファイルを1つのブックにまとめるマクロの導入手順」をご紹介していきます。

いくつかの手順に分けてご紹介しますので、ひとつひとつ順番に実践してみてくださいね。

 

 複数のエクセルを1つにまとめるマクロの導入手順

1.エクセルファイルを用意する

2.コードを「標準モジュール」に貼り付ける

3.実行ボタンを設置

 

手順①:エクセルファイルを用意する

 

まずはマクロを実行するためのエクセルファイルを用意しましょう。

 

1.「ファイル」タブを選択

 

2.「名前を付けて保存」を選択

 

3.保存したいフォルダを選択し、ダイアログを開く

 

4.「ファイルの種類」⇒「Excel マクロ有効ブック(*.xlsm)」⇒「保存」

 

5.タブエリアで右クリック ⇒ 「リボンのユーザー設定」を選択

 

6.「開発」にチェックをいれる ⇒ OK

 

7.タブエリアに「開発」が入っていることを確認しましょう!

 

以上で、エクセルマクロを実行するためのファイルを準備できました。

次はいよいよ、コードを挿入していきますよ!

といっても難しくないので、ひとつひとつやっていきましょう。

 

手順②:コードを「標準モジュール」に貼り付ける

 

1.「開発」タブ ⇒ 「Visual Basic」を選択し、VBE(Visual Basic Editor)を起動する

※ Alt + F11 でも起動できます。

起動すると、以下のような真っ白な画面が出てきます!

 

2.「挿入」タブ ⇒ 「標準モジュール」を選択し、標準モジュールを挿入する

 

「Module 1」が追加されたことを確認しましょう。

 

3.下記コードをすべてコピーする

 

4.VBEの右のウィンドウに貼り付ける ⇒ 上書き保存

 

 

 

以上で、コードの貼り付けが完了しました!

これで8割方終わったといってもいいでしょう。

ですが、最後の手順までもう少しお付き合いください。

 

手順③:実行ボタンを設置

 

最後のひと手間を加えて、実行しやすい環境を整えていきましょう。

 

1.「開発」タブ ⇒ 「挿入」 ⇒ 「ボタン(フォームコントロール)」

画面の適当なところでクリックしてみると・・・

「マクロの登録」というウィンドウが開きます。

 

2.「複数ブックまとめる」 ⇒ 「OK」

ボタンにマクロの実行名を紐づける、といったことを行います。

 

 

お疲れさまでした!

このようなボタンが画面上に挿入されたら、完成です!(ボタンに表示されているテキストは、自由に変えても大丈夫です)

では次に「複数のエクセルファイルを1つのブックにまとめる(シート別)マクロの使い方」について解説していきます。

 

複数のエクセルファイルを1つのブックにまとめる(シート別)マクロの使い方

 

さて、ここまでは「複数のエクセルファイルを1つのブックにまとめる(シート別)マクロの導入手順」を解説しました。

ここからは実際の使い方をご説明してきます。

使い方はとてもシンプルですので、安心してくださいね!

 

フォームボタンを押して、ダイアログを開く

 

さきほど作成したExcelマクロファイルを開いた状態からスタートしましょう。

「ボタン1」を押しダイアログを開いてみましょう。

ボタンを押すと、上図のようなダイアログが開かれます。

複数のエクセルをひとつにまとめるため、エクセルが保存されている該当のフォルダに移動しましょう。

 

ダイアログから、複数のExcelブックを選択する

 

ダイアログから選択する方法はいくつかあります。

以下の4つから試してみてくださいね。

 

 複数のエクセルを選択するための4つの方法

■選択方法①:マウスドラッグで選択

■選択方法②:「左クリック」+「ctrl」で、エクセルを部分的に選択

■選択方法③:「左クリック」+「Shift」で、エクセルをまとめて選択

■選択方法④:「ctrl」+「A」で、すべてのエクセルを選択

 

以上が「複数のエクセルファイルを1つのブックにまとめるマクロ」の使い方になります。

 

ですが、1つのエクセルファイル内に大量の複数シートが挿入されることによって、シート間の移動がめんどくさくなりませんか?

たとえばシート数が100個くらいになると、そもそもどういった名前のシートが挿入されているのか把握しづらいし管理が大変になりますよね…。

1枚のシートに全シート分の目次などが一覧化されると便利なんですが…。

 

ということで、Excelで目次を自動生成する方法をマクロで解説しました。

こちらも実行ボタン一つだけで、ハイパーリンク付き目次を一括作成することができます!

これによって、複数シート間の移動がめちゃくちゃ楽になり、あなたの業務効率化をさらに加速させることが可能です。

>>Excelで目次を自動生成する方法は?ページ番号の振り方も解説!

 

複数のエクセルファイルを1つのブックにまとめる(シート別)マクロの解説!

 

さきほどは、Excelで複数のブックやシートをまとめるマクロの使い方をご紹介しました。

ここからは「マクロの使い方は分かったけど、マクロの解説もしてほしい!!」という方に向けて、コードの解説をしていきたいと思います。

といってもコード量も少ないですし、難しくはありません。

実は、たった2つのポイントを押さえるだけで、簡単に作成できるんですよ!

 

 「Excelで複数ブックやシートをまとめるマクロ」の2つのポイント

1.Application.GetOpenFilenameメソッドで、MultiSelect:=Trueにする

2.WorkSheets.Moveメソッドを使う

以上2つになります。

ということで、各ポイントを解説していきますね。

 

ポイント①:Application.GetOpenFilenameで、MultiSelect:=Trueにする

ポイント①となる部分は、2行目の以下コードです。

arrFileName = Application.GetOpenFilename(“Excelブック,*.xls?”, MultiSelect:=True)

 

以上のように記述すると、ダイアログから複数のブックを選択できるようになります。

こちらのコードについて、ひとつひとつ説明していきます。

 

■選択方法の指定

Application.GetOpenFilenameメソッドには、ブックを「ひとつだけ選択」または「複数選択」するか指定することができます。

具体的には、

  • ひとつだけ選択 ・・・ 記述を省略、またはMultiSelect:=FalseとすればOK
  • 複数選択    ・・・ MultiSelect:=TrueとすればOK

「True」か「False」にするだけですから、とてもシンプルですよね。

 

■変数の型

ただし、ダイアログで選択した複数ブックのパスを格納する「arrFileName」という変数は、「Variant」型としておく必要があります。

なぜかといいますと、Application.GetOpenFilenameメソッドで、複数ブックを選択した場合の戻り値は、「配列(Variant)」となるからですね。

 

■オブジェクトブラウザー
ちなみにオブジェクトブラウザーを確認すると、戻り値の部分に「(~~) as Variant」にはなっていません。

ですから、戻り値の型が分かりづらいんですよね。

その後、For Each In ~ Nextで、取得したブックのパス(配列)を1個ずつ処理していきます。

変数「Flag」にて、「1回目に開いたブック」、「2回目以降に開いたブック」かを判定する処理を入れています。

 

ポイント②:WorkSheets.Moveメソッドを使う

ポイント②となるのは、17行目の以下コードです。

wbTo.Worksheets.Move After:=wbFrm.Worksheets(wbFrm.Worksheets.Count)

以上のように記述してみましょう。

 

■ポイント②のコードを端的に説明すると

端的に表現するなら、

「2回目以降に開いたブックを、1回目に開いたブックに移動する」

ということになります。

■Worksheets.Moveメソッド

コード左側の、Worksheets.Moveメソッドは、「Worksheets(全シート).Move(移動する)」という風に読み替えて理解すればOKです。

そして今回のコードの意図としては、「2回目以降に開いたブックを1回目に開いたブックに移動する」ですよね。

ということはまず、「wbTo.Worksheets.Move」と書き込んでみましょう。

日本語で言い換えれば、「wbTo(2回目以降のブックの).worksheets(全シート).Move(移動する)」という意味になりますね。

 

■名前付き引数の指定

そして、名前つき引数※にて「Before」「After」のどちらに移動するかを指定しましょう。

※名前付き引数は、コロンイコールで記述します。(:=)

今回は、Afterとしてみましょう。

Afterの後に記述されているのは、「どこのブックの、どこのシートの前後に移動するか」というものになります。

つまり、

  • どこのブック・・・wbFrm【1回目に開いたブックの】
  • どこのシート・・・Worksheets(wbFrm.Worksheets.Count)【wbFrmの末尾のシートの】

 

と、言い換えることができます。

■シートの末尾の設定方法

ここでポイントになるのが、Worksheets(wbFrm.Worksheets.Count)としているところです。

つまり、2回目以降に開いたブックを、1回目に開いたブックへ移動すると、1回目に開いたブックのシート数が更新されてしまいますよね。

それを「Worksheetsコレクション.Countプロパティ」にて、1回目に開いたブックの現在の全シート数を毎回取得することで、末尾を指定することができます。

 

ご質問への回答

 

ここからは、頂いたご質問へ回答をさせていただきます!

情報のご共有ありがとうございます!
毎週、毎月このような業務を行っているので、とても時間短縮になりました。

追加で、もしできたらいいなぁ、と思う事なのですが、
結合前のブック名を、そのままシート名に名づけることはできないでしょうか?

日時:2022年10月4日
ご質問:しょー 様より

 

ご質問、ありがとうございます。

さっそく作成してみましたので、以下コードをそのままコピーして差し替えて実行してみてください!

 

 

こちらのコードに差し替えて、再度実行いただくと、以下のような感じになります。

■結合前(例)

ブック1:a.xlsx ⇒ シート1:Sheet1
ブック2:b.xlsx ⇒ シート1:Sheet1 シート2:Sheet2
ブック3:c.xlsx ⇒ シート1:Sheet1
ブック4:d.xlsx ⇒ シート1:Sheet1

上記ブックやシートがあったとして、こちらを結合すると・・・

■結合後(例)

ブック1:a.xlsx

⇒ シート1:Sheet1 シート2:b1 シート3:b2 シート4:c シート5:d

 

以上の通り、2回目以降に開いたブック名をそのままシート名に設定できます。

ただし、「.xlsx」や「.xls」といった拡張子はおそらく不要かな?と思いましたので、省いています。

また、2回目以降に開いたブックに、複数シート存在(例のb.xlsxです)している場合を想定して、結合したシート名に「b1」や「b2」のように、「ブック名+シートカウント数」を付与しております。

なぜかというと、Excelは一つのブックに同じシート名が存在してはいけないルールがあるため、このような処理としてみました!

________________________________

ご質問への回答は以上ですが、このほかにも作ってほしいマクロ・解説してほしいマクロなどがありましたら、ぜひコメント欄へどうぞ!

筆者はExcelVBA(マクロ)だけで2000時間ほどの業務効率化を達成した実績がありますので、大体なんでも作成できたりします。

お気軽にお問い合わせくださいませ!^^

 

複数のエクセルファイルを1つのブックにまとめる(シート別)マクロのまとめ

 

今回は「複数のエクセルファイルを1つのブックにまとめる(シート別)マクロ」について解説しました。

今回のマクロでだいぶ業務効率化できると思うのですが…。

しかし、複数のエクセルファイルを1つのブックにまとめることによって、シート枚数が半端じゃない数になり、管理が大変になるといったデメリットもあるんですよね。

 

そこで、Excelで目次を自動生成する方法を解説していますので、こちらもマクロを使って解決しちゃいましょう。

>>Excelで目次を自動生成する方法は?ページ番号の振り方も解説!

 

今後もVBAに関するお役立ち情報を紹介していきたいと思いますので、お楽しみに!

今回は以上になります。最後までご覧いただきありがとうございました。

8 件のコメント

  • 情報のご共有ありがとうございます!
    毎週、毎月このような業務を行っているので、とても時間短縮になりました。

    追加で、もしできたらいいなぁ、と思う事なのですが、
    結合前のブック名を、そのままシート名に名づけることはできないでしょうか?

    • しょー様

      お問合せありがとうございます!
      コンテンツ内でご質問へ回答しておりますので、よろしければご覧くださいませ^^

  • 非常に参考になりました。
    ただ、統合するとシートの改ページなどは変わってしまうようです。シートを移動、コピーすると印刷設定がかわることが課題になっており、今回ので設定までは引き継げましたが、まんま同じような印刷にはなりませんでした。

    またシート名も引き継げるといいなと思います。

    可能でしたら、お答え頂けますと幸いです。

  • 使いやすく素晴らしいマクロをありがとうございます!
    結合前のブック名をシート名にして統合しているものを使わさせていただいています。
    出来ればなのですが、指定の言葉をブック名にし、結合前のブック名をシート名にして統合(1つ目のファイルから)できませんでしょうか?
    お手すきの時にご回答お願いいたします。

  • 特定の名前から始まるシートのみをピックアップしたいです。(申込シートのみ。その他関係ないシートは除外したいです。)
    どのようにすれば実現可能か教えて頂けませんでしょうか。

  • 教えてください。
    二つのVBAと目次を作成するVBAを一つのエクセルファイルで使用することはできないんでしょうか。

  • 参考にさせていただいております。
    下記のように複数ブック、シートがある中から、指定のシート、またはアクティブのシートのみをコピーすることは可能でしょうか?
    シート名を指定する場合は指定のセルを参照したいと思っております。

    ■結合前
    ブック1:a.xlsx ⇒ シート1:Sheet1 シート2:Sheet2
    ブック2:b.xlsx ⇒ シート1:Sheet1 シート2:Sheet2
    ブック3:c.xlsx ⇒ シート1:Sheet1 シート2:Sheet2
    ブック4:d.xlsx ⇒ シート1:Sheet1 シート2:Sheet2

    ■結合後(選択ブックのSheet1のみをコピー)
    ブック1:a.xlsx
    ⇒ シート1:a.Sheet1 シート2:b.Sheet2 シート3:c.Sheet1 シート4:d.Sheet1

    お手すきの際にご回答いただけますと幸いです。
    よろしくお願いいたします。

  • はじめまして。ゆうと申します。
    マクロ勉強中で簡単なものは作れるのですが、以下のようなものを作りたいです。
    自分の今の知識、技量では無理なのでどうかご教授いただければ幸いです。
    【相談事項】
    7~10個の各ブック内のフォーマットへ記入されている部分をコピーして一つのシートにまとめるマクロ

    ※各人がエクセルのフォーマットに記入したものを見やすい様にするため、
     一つのシートに一覧表みたいにまとめたいと思っております。

    わかりずらくて申し訳ありませんがお目通しいただければ幸いです。

  • みと へ返信する コメントをキャンセル

    メールアドレスが公開されることはありません。