複数のエクセルファイルを1つのブックにまとめるVBA【 設定方法を紹介】

疑問がある人
こんな疑問にお答えします。
結論から言うと、他のエクセルファイルを開く・シートをコピーする・元のエクセルファイルに貼り付けるという処理をするマクロを導入する!です。
VBAを使えば、エクセルファイルがいくつあっても一瞬で1つのエクセルファイルにデータを集約することができます。
私の職場では、シートをコピーして別のブックに貼り付ける!という業務があり時間をかけて手作業で処理をしていましたが、複数のエクセルファイルを一つにまとめるVBAを導入してからは完全自動化することができました。

上を目指す人
今まで当たり前と思い込んでいた作業がとんどでもなく効率の悪い方法だったと全員が認識し、今ではシートのコピペ作業はVBAを使って一瞬で処理することが当たり前になっています。
この記事では、複数のエクセルファイルを1つのまとめるVBAの特徴、導入方法について詳しくご紹介しています。コピペして使えるサンプルVBAを掲載しているので是非使ってみてください。
目次
複数のエクセルファイルを1つのブックにまとめるVBAとは?
エクセルファイルを1つにまとめるとは、指定したブックのシートをコピーして、1つのブックに集約する!ということです。
エクセルファイルを1つにまとめるVBAの流れ
- 指定したブックを開く
- ブック内の全シートをコピー
- VBAが設定されているブックに貼り付け
- 指定したブックを閉じる
- 1~4の繰り返し
上記のように大きく分けて5つの項目で構成されています。
完成したVBAの動きを見ると複雑で難しいと感じるかもしれませんが、項目を分けて考えてみると複雑なVBAは一切使われていません。

ひらめく人
今回紹介する複数のエクセルファイルを一つにまとめるVBAには2つの種類があります。
1つ目・・・1つのブックを指定してシートを集約するVBA
→ピンポイントで情報を集約できる
2つ目・・・フォルダを指定してフォルダ内にある全てのブックを集約するVBA
→まとめて多くの情報を集約できる

作業する人
1つのブックを指定してシートを集約するVBAの解説
1つのブックを指定してシートを集約するVBAとはどういうものかをご紹介していきます。
下記の動画では、1つのブックを指定してシートを集約するVBAの動作を確認することができます。
指定したブックにある全てのシートをVBAが設定されたブックに集約することができます。
「ブックを開く → シートをコピー → VBAが設定されたブックに貼り付け」といった単純作業を繰り返します。
コピペするシートが一つや二つの場合、時間をかけてVBAを設定する必要はありませんが、コピペしたいシートの数が多くなるとVBAを使って自動化してしまうのがおすすめです。

上を目指す人
1つのブックを指定してシートを集約するVBAの導入方法
1つのブックを指定してシートを集約するVBAの導入方法を詳しく説明していきます。
手順通り設定すればVBAを使ってシートを集約することができるはずです。是非試してみてください。
導入方法① シートの設定
上図のように「設定」シートを作成します。
B3セル~B20セルの背景色を白、それ以外は黄色にしています。
これは、「ブックの場所を記載するセルがB3セル~B20セルである」ということをわかりやすくするためです。
今回はB3セル~B20セルの情報を読み取るプログラムになっているので、ブックの場所を記載するセルを勝手に変更しないでください。
シートの名前を「設定」にしてください。シートを区別するVBAが設定されているので、シート名を「設定」にしなければ正常に動作しません。
下記の記事では、シート名を区別するVBAについて詳しく解説されています。

ガッツポーズの人
導入方法② VBAの作成
下記で紹介しているサンプルVBAを標準モジュールにそのまま貼り付けてください。
これでボタンにマクロの登録をするとき、「テスト」というマクロが表示されるようになります。
導入方法③ ボタンの作成
「ボタン」は「開発」タブの中にあります。
VBAを導入する場合「ボタン」コマンドの使用頻度が高いので、私はショートカットを作成していつでも使えるようにしています。
下記の記事では、使用頻度の高いコマンドのショートカットの作り方について詳しく解説しています。

ガッツポーズの人
ボタンを作り、「右クリック」 → 「マクロの登録」 → 「テスト」 → 「OK」でブックを指定してシートを集約するVBAをボタンに設定することができます。
表示している「ボタン」のテキストを「実行」に変更します。「ボタン」の上で、「右クリック」→「テキストの編集」でテキストを変更することができます。
1つのブックを指定してシートを集約するVBAは下記のサンプルVBAをコピーしてすぐに使うことができます。
サンプルVBA(1つのブックを指定してシートを集約するVBA)
1 | Sub テスト() |
2 | Application.ScreenUpdating = False |
3 | Dim Abook As Workbook |
4 | Set Abook = ActiveWorkbook |
5 | Set Ash = Abook.Worksheets(“設定") |
6 | For k = 3 To 20 |
7 | Dim exfile As String |
8 | If Ash.Cells(k, 2) = “" Then |
9 | Exit Sub |
10 | Else |
11 | exfile = Ash.Cells(k, 2) |
12 | End If |
13 | Workbooks.Open Filename:=exfile |
14 | If Err.Number > 0 Then |
15 | MsgBox “ファイルはすでに開かれています。" |
16 | Exit Sub |
17 | End If |
18 | Dim Bbook As Workbook |
19 | Set Bbook = ActiveWorkbook |
20 | For i = 1 To Bbook.Worksheets.Count |
21 | Bbook.Worksheets(i).Copy After:=Abook.ActiveSheet |
22 | Abook.ActiveSheet.Name = Abook.ActiveSheet.Name & “(" & Bbook.Name & “)" |
23 | Next i |
24 | Bbook.Close |
25 | Next k |
26 | End Sub |
上記サンプルVBAでは、他のエクセルファイルを操作するVBAが使われています。このVBAに関してもっと詳しく知りたい!という方は下記の記事を参考にしてください。

ガッツポーズの人
1つのブックを指定してシートを集約するVBAの使い方
使い方① ブックの指定
上図のようにB3セル~B20セルに、集約したいシートが保存されているブックの保存場所を記入します。最後に記入するブックの名前には、拡張子までしっかり記載することに注意が必要です。
使い方② 実行ボタンをクリック
ブックを指定してシートを集約するVBAが登録されたボタンをクリックしてください。

ガッツポーズの人
実行結果(ブックを指定してシートを集約するVBA)
ブックを指定してシートを集約するVBAを実行すると上図のようにシートが自動的に作成されます。
私は、このVBAを使って約50シート分の集約を行ったことがあります。
VBAの処理が完了するまで1分程度時間がかかりましたが、問題なく集約することができました。
下記のサイトでは、ブックを指定してシートを集約するVBAが設定されたエクセルファイルをダウンロードしてお使いいただくことができいます。
設定が難しい!すぐにVBAを使ってみたい!という方は是非サイトを参考にしてみてください。
ブックが保存されているフォルダを指定してシートを集約するVBAの解説
ブックが保存されているフォルダを指定してシートを集約するVBAとはどういうものかをご紹介していきます。
下記の動画ではブックが保存されているフォルダを指定してシートを集約するVBAの動作を確認することができます。
ブックが保存されているフォルダを指定してシートを集約するVBAは、上記で紹介しているブックを指定してシートを集約するVBAと処理内容はほとんど変わりません。
違う点は、フォルダ内の全てのブックを指定することができるので、いっきにシートを集約することが可能!ということです。

ガッツポーズの人
ブックが保存されているフォルダを指定してシートを集約するVBAの導入方法
導入方法① シートの設定
上図のように「設定」シートを作成します。
B3セル~B20セルの背景色を白、それ以外は黄色にしています。これは、フォルダの場所を記載するセルがB3セル~B20セルであるということをわかりやすくするためです。
フォルダを指定してブック内のシートを集約するVBAはB3セル~B20セルの情報を読み取る設定になっているので、フォルダの場所を記載するセルを勝手に変更してはいけません。
シートの名前を「設定」にしてください。シートを区別するVBAが設定されているためシート名を「設定」にしなければ正常に動作しません。
導入方法② VBAの作成
下記で紹介しているサンプルVBAを標準モジュールにそのまま貼り付けてください。
これでボタンにマクロの登録をするとき、「テスト」というマクロが表示されるようになります。
導入方法③ ボタンの作成
「ボタン」は「開発」タブの中にあります。
ボタンを作り、「右クリック」 → 「マクロの登録」 → 「テスト」 → 「OK」でブックが保存されているフォルダを指定してシートを集約するVBAをボタンに設定することができます。
ブックが保存されているフォルダを指定してシートを集約するVBAは、下記のサンプルVBAをコピーしてすぐに使うことができます。
サンプルVBA(ブックが保存されているフォルダを指定してシートを集約するVBA)
1 | Sub テスト() |
2 | Application.ScreenUpdating = False |
3 | Dim Abook As Workbook |
4 | Set Abook = ActiveWorkbook |
5 | Dim Ash As Worksheet |
6 | Set Ash = Abook.Worksheets(“設定") |
7 | For k = 3 To 20 |
8 | Dim exfile, exname As String |
9 | If Ash.Cells(k, 2) = “" Then |
10 | Exit Sub |
11 | Else |
12 | exfile = Ash.Cells(k, 2) |
13 | End If |
14 | exname = Dir(exfile & “\*.xlsm*") |
15 | Do Until exname = “" |
16 | Workbooks.Open Filename:=exfile & “\" & exname |
17 | If Err.Number > 0 Then |
18 | MsgBox “ファイルはすでに開かれています。" |
19 | Exit Sub |
20 | End If |
21 | Dim Bbook As Workbook |
22 | Set Bbook = ActiveWorkbook |
23 | For i = 1 To Bbook.Worksheets.Count |
24 | Bbook.Worksheets(i).Copy After:=Abook.ActiveSheet |
25 | Abook.ActiveSheet.Name = Abook.ActiveSheet.Name & “(" & Bbook.Name & “)" |
26 | Next i |
27 | Bbook.Close |
28 | exname = Dir |
29 | Loop |
30 | Next k |
31 | End Sub |
14行目は関数Dirを使ってフォルダ内のデータを取得しています。14行目の意味は、変数exfileで指定されたフォルダ内の.xlsmが含まれるデータ全てを取得する!というもの。拡張子.xlsx形式のエクセルファイルを取得するには14行目の .xlsm を .xlsx に変更する必要があります。
また14行目の .xlsm を .xl に変更すると、全てのエクセルファイルのデータを取得することが可能です。取得するファイル形式に合った拡張子に編集して試してみてください。
15行目から29行目はDo〜Loopによって関数Dirでヒットした全てのエクセルファイル名を取得する!というものです。
Do~Loopと関数Dirを組み合わせることによって、フォルダ内の全ファイル名を取得することができます。この方法は他にもいろんなことに応用することができるので是非使い方をマスターしておきましょう。

ひらめく人
ブックが保存されているフォルダを指定してシートを集約するVBAの使い方
使い方① フォルダの指定
上図のようにB3セル~B20セルに、集約したいシートが保存されているフォルダの保存場所を記入します。
「サンプル」と「サンプル2」というフォルダの2つに保存されている全ブックの全シートをまとめて集約することができます。

拒否する人
使い方② 実行ボタンをクリック
ブックが保存されているフォルダを指定してシートを集約するVBAが登録されたボタンをクリックしてください。
実行結果(ブックが保存されているフォルダを指定してシートを集約するVBA)
ボタンをクリックして、ブックが保存されているフォルダを指定してシートを集約するVBAを実行すると上図のようにシートが自動的に作成されます。
このVBAでは、知らず知らずのうちに大量のシートを集約する処理を実行してしまう恐れがあります。
指定するフォルダ内にブックがどれだけ保存されているか確認してから実行するように注意しましょう。
下記のサイトでは、フォルダ内のブック情報を集約するVBAが設定されたエクセルファイルをダウンロードしてお使いいただくことができいます。
設定が難しい!すぐにVBAを使ってみたい!という方は是非サイトを参考にしてみてください。
複数のブックをまとめるマクロは仕事でかなり使える!
複数のブックを一つにまとめるVBAを紹介してきました。
やり方をマスターをすればサーバー内にあるあらゆるエクセルファイルを一瞬で編集できるようになります。このマクロは実際の業務でかなり使えるマクロなので自由に扱えるようにしておきましょう。
下記の記事では、VBA参考書の選び方やポイントについて詳しく紹介しています。別シートに情報を自動反映するエクセルマクロもVBA参考書を使って開発したものです。
仕事の効率アップにつながるマクロを開発してみたい!と考えている方は是非参考書で読んでみて下さい。あなたの考えを表現するためのVBAがきっと見つかると思います。
参考書では、シートから別のブックに情報を自動反映させるマクロの作り方も掲載されています。
下記の記事では、他のブックを操作するマクロについて詳しく紹介しています。
今回紹介した複数のブックを一つにまとめるVBAと組み合わせることで、エクセル作業の効率化に繋がるマクロをどんどん開発することができるようになります。
エクセル作業の効率化マクロを導入することで、ミスが激減し大幅な作業時間の削減に繋がります。
エクセルVBAの導入を検討している方は参考にしてみてください。
これからVBAの勉強をはじめるには
複数のエクセルファイルを一つにまとめるVBAのように、VBAには仕事の効率をアップさせることができる機能がいろいろあります。VBAのプログラミングスキルを身に着けることで、今まで何時間もかかかっていた作業をほんの数分で処理することができるようになります。
VBAを基本から学びたい!という方におすすめなのが、オンラインプログラミング学習サービス【1st Step】です。
【1st Step】は初心者のためのプログラミング学習サービスで、C言語・java・VBAの3つのプログラミングの基礎をオンラインで勉強することができます。
- 基礎からプログラミングを勉強したい
- まとまった勉強時間の確保が難しい
- 途中で挫折したくない
上記のように考えている方に【1st Step】はおすすめです。
下記の記事では、オンラインプログラミング学習サービス【1st Step】の特徴や私の体験など詳しく紹介しています。
これからVBAの勉強を始めようと考えている方は是非参考にしてみてください。
https://note.com/mamemametochan/n/n19affe6628cf
ディスカッション
コメント一覧
まだ、コメントがありません