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

疑問がある人

複数のエクセルファイルを1つのエクセルファイルにまとめたい。どうやればいいの？

こんな疑問にお答えします。

結論から言うと、他のエクセルファイルを開く・シートをコピーする・元のエクセルファイルに貼り付けるという処理をするマクロを導入する！です。

VBAを使えば、エクセルファイルがいくつあっても一瞬で１つのエクセルファイルにデータを集約することができます。

私の職場では、シートをコピーして別のブックに貼り付ける！という業務があり時間をかけて手作業で処理をしていましたが、複数のエクセルファイルを一つにまとめるVBAを導入してからは完全自動化することができました。

上を目指す人

作業効率が格段にアップ！他の業務に専念できるようになった

今まで当たり前と思い込んでいた作業がとんどでもなく効率の悪い方法だったと全員が認識し、今ではシートのコピペ作業はVBAを使って一瞬で処理することが当たり前になっています。

この記事では、複数のエクセルファイルを１つのまとめるVBAの特徴、導入方法について詳しくご紹介しています。コピペして使えるサンプルVBAを掲載しているので是非使ってみてください。

1 複数のエクセルファイルを1つのブックにまとめるVBAとは？
2 １つのブックを指定してシートを集約するVBAの解説
3 ブックが保存されているフォルダを指定してシートを集約するVBAの解説
4 複数のブックをまとめるマクロは仕事でかなり使える！
5 これからVBAの勉強をはじめるには

複数のエクセルファイルを1つのブックにまとめるVBAとは？

エクセルファイルを１つにまとめるとは、指定したブックのシートをコピーして、１つのブックに集約する！ということです。

エクセルファイルを１つにまとめるVBAの流れ

指定したブックを開く
ブック内の全シートをコピー
VBAが設定されているブックに貼り付け
指定したブックを閉じる
1～4の繰り返し

上記のように大きく分けて５つの項目で構成されています。

完成したVBAの動きを見ると複雑で難しいと感じるかもしれませんが、項目を分けて考えてみると複雑なVBAは一切使われていません。

ひらめく人

上記のVBAの流れに意識しながらサンプルVBAを確認すると理解しやすい

今回紹介する複数のエクセルファイルを一つにまとめるVBAには2つの種類があります。

１つ目・・・１つのブックを指定してシートを集約するVBA

→ピンポイントで情報を集約できる

２つ目・・・フォルダを指定してフォルダ内にある全てのブックを集約するVBA

→まとめて多くの情報を集約できる

作業する人

業務内容によって２種類のVBAを使い分けると便利です。

１つのブックを指定してシートを集約するVBAの解説

１つのブックを指定してシートを集約するVBAとはどういうものかをご紹介していきます。

下記の動画では、１つのブックを指定してシートを集約するVBAの動作を確認することができます。

指定したブックにある全てのシートをVBAが設定されたブックに集約することができます。

｢ブックを開く → シートをコピー → VBAが設定されたブックに貼り付け｣といった単純作業を繰り返します。

コピペするシートが一つや二つの場合、時間をかけてVBAを設定する必要はありませんが、コピペしたいシートの数が多くなるとVBAを使って自動化してしまうのがおすすめです。

上を目指す人

一度VBAを設定すると今後ずっと使い続けることができます。シートのコピペ作業が多い！という方におすすめです。

実際にVBAを使ってシートを集約したのが下記の画像です。

13シート分の情報を一瞬で集約することができました。

追加されるシートの名称は、「シート名+ファイル名」となる設定になっているのでシート名が被る心配がありません。

１つのブックを指定してシートを集約するVBAの導入方法

１つのブックを指定してシートを集約するVBAの導入方法を詳しく説明していきます。

手順通り設定すればVBAを使ってシートを集約することができるはずです。是非試してみてください。

導入方法① シートの設定

上図のように｢設定｣シートを作成します。

B3セル～B20セルの背景色を白、それ以外は黄色にしています。

これは、「ブックの場所を記載するセルがB3セル～B20セルである」ということをわかりやすくするためです。

今回はB3セル～B20セルの情報を読み取るプログラムになっているので、ブックの場所を記載するセルを勝手に変更しないでください。

シートの名前を「設定」にしてください。シートを区別するVBAが設定されているので、シート名を「設定」にしなければ正常に動作しません。

下記の記事では、シート名を区別するVBAについて詳しく解説されています。

mamemametochan.com

【エクセルマクロ】別シートに情報を自動反映させるマクロの作り方・導入方法を紹介

https://mamemametochan.com/excel-8

豆父ちゃんシートからシートに必要な情報を転記する方法ってないの？こんな疑問にお答えします。結論から言うと、シート名を取得するVBAを使って、別シートに情報を自動反映させるマクロを作成する！です。シート名を取得する方法さえ理解すれば、別シートに情報を自動反映させるマクロは簡単に作成することができます。この記事では、画像や動画を使って初心者の方でも簡単に出来る転記マクロの作り方を紹介します。 (adsbygoogle = window.adsbygoogle || ).push({});別シートに情報を自動反映させるマクロとは？別シートに情報...

ガッツポーズの人

シート名を区別するVBAはエクセル作業の効率をアップさせるVBAには欠かせない技術です。是非参考にしてみてください。

導入方法② VBAの作成

下記で紹介しているサンプルVBAを標準モジュールにそのまま貼り付けてください。

これでボタンにマクロの登録をするとき、｢テスト｣というマクロが表示されるようになります。

導入方法③ ボタンの作成

「ボタン」は「開発」タブの中にあります。

VBAを導入する場合「ボタン」コマンドの使用頻度が高いので、私はショートカットを作成していつでも使えるようにしています。

下記の記事では、使用頻度の高いコマンドのショートカットの作り方について詳しく解説しています。

mamemametochan.com

エクセル業務効率化【激的に仕事が早くなる！おすすめの方法3選】

https://mamemametochan.com/excel-24

エクセルはデスクワークの仕事の中で最も使用頻度の高いツールですよね。豆父ちゃん私の仕事は7割がデスクワーク、そのうちデスクワークのほとんどがExcelを使用しているデスクワークをするうえでエクセルを使えるということがあたりまえの時代です。エクセル業務をどれだけ効率的に処理をできるかが重要です。エクセル業務を効率的に処理できる人とできない人とでは、エクセル業務のスピードが2倍から3倍変わることも。。実際に私の職場では時間と手間のかかる単純な作業のエクセル業務が多くあります。そんな単純...

ガッツポーズの人

エクセル作業の効率をアップさせたい方は是非参考にしてみてください。

ボタンを作り、｢右クリック｣ → ｢マクロの登録｣ → ｢テスト｣ → ｢OK｣でブックを指定してシートを集約するVBAをボタンに設定することができます。

表示している「ボタン」のテキストを「実行」に変更します。｢ボタン｣の上で､｢右クリック｣→｢テキストの編集｣でテキストを変更することができます。

１つのブックを指定してシートを集約するVBAは下記のサンプルVBAをコピーしてすぐに使うことができます。

サンプルVBA(１つのブックを指定してシートを集約する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に関してもっと詳しく知りたい！という方は下記の記事を参考にしてください。

mamemametochan.com

複数のエクセルを操作する方法【開く・操作する・閉じるを行うVBA 】

https://mamemametochan.com/excel-13

豆父ちゃん複数のエクセルファイルを同時に扱う業務の効率をもっと上げたい。なにかいい方法はないの？このように考えている方におすすめの方法が、複数のエクセルファイルを開く・操作する・閉じる処理を行うVBAです。デスクワークでは、複数のエクセルファイルを同時に扱う業務がいっぱいですよね。似たような内容のExcelデータを見比べる提出用のエクセルファイルと管理用のエクセルファイルを見比べるエクセルファイル内にSheetがいっぱいあるとき、2つ開く複数のエクセルファイルを扱う業務長時間行っていると作業...

ガッツポーズの人

これで１つのブックを指定してシートを集約するVBAの導入ができます。簡単に設定できますので試してみてください

１つのブックを指定してシートを集約するVBAの使い方

使い方① ブックの指定

上図のようにB3セル～B20セルに、集約したいシートが保存されているブックの保存場所を記入します。最後に記入するブックの名前には、拡張子までしっかり記載することに注意が必要です。

使い方② 実行ボタンをクリック

ブックを指定してシートを集約するVBAが登録されたボタンをクリックしてください。

ガッツポーズの人

ブックの場所を記載して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とブックが保存されているフォルダを指定してシートを集約するVBAを比較すると違いがよくわかります