【VBA】シートコピペを自動化！複数ブックを集約するマクロの作り方

 

複数のエクセルファイルを開いて、コピーして、貼り付けて、閉じる。

この作業を毎日繰り返していませんか？

 

VBAを使えば、フォルダに保存されている全てのブックの全シートを、ボタン1クリックで自動集約できます。

この記事では、複数ブックのシートを1つのエクセルファイルにコピペする「ブック集約マクロ」の仕組みと実際のVBAコードを、プログラミング初心者でも理解できるように解説。

勤務整理情報の集約、超勤整理簿のとりまとめ、設備の検査データ集計など、あらゆるエクセル業務に応用できる内容になっているので、ぜひ最後まで読んでみてください。

 

手作業でブックを開いてコピペしている人が陥る3つの問題

エクセルの集約作業は、やり方を間違えると大量の時間と労力を消耗します。

特に「担当者ごとに別ファイルで管理されているデータを毎月1つにまとめる」という業務は、多くの職場が行っている処理です。

 

手作業でブックを1つずつ開いてコピペしている方は、次の3つの問題を抱えているかどうか確認してみてください。

 

問題①：ファイル数が増えるほど作業時間が爆発的に増える

ファイルが5件なら10分で終わる作業も、50件になると1時間以上かかります。

月次・週次でこの作業が繰り返されるとなると、年間で何十時間もの時間が消えていく計算です。

私が以前受注したマクロ開発の依頼でも、「職員50名分の勤務整理簿を毎月手作業で集約しているので、どうにかしたい」という相談がありました。

 

担当者がすべて手作業でやっていたため、集約作業だけで毎月3～4時間かかっていたそうです。

 

問題②：コピペミスや貼り付け先の間違いが発生する

ファイルを開くたびに「どこまでコピーしたか」「どのシートに貼ったか」を確認しながら作業するのは、集中力が続きません。

10件、20件と処理していくうちに、コピー範囲がずれたり、同じデータを2回貼ったりというミスが起きます。

特に疲れているときや急いでいるときほど、こういったコピペミスは発生しやすくなるので注意が必要です。

 

問題③：手順書がないと他の人に任せられない

「このファイルはあのフォルダから持ってきて、このシートに貼って…」という手順を頭の中で管理していると、担当者が変わったときに業務が止まります。

手順書を作るにも時間がかかりますし、手順書を作ったとしても手作業である以上ミスのリスクはゼロにはなりません。

VBAを使えば、こういった問題をすべて解決することが可能。

 

フォルダを指定するだけで、フォルダ内の全ブックの全シートを自動でコピペして集約する仕組みを一度作ってしまえば、誰でもボタン1クリックで同じ作業を再現できるようになります。

 

ブック集約マクロの仕組みを理解する

実際のVBAコードを見る前に、ブック集約マクロがどういう仕組みで動くのかを理解しておきましょう。

仕組みを理解してからコードを読むと、格段に覚えやすくなります。

 

集約マクロの処理フロー

ブック集約マクロの処理は、大きく4つのステップで構成されています。

 

この4つのステップを、フォルダ内の全ファイルに対してループ処理で繰り返すのがブック集約マクロの基本的な仕組みです。

手作業でやっていた「開く→コピペ→閉じる」という一連の操作を、VBAが自動で繰り返してくれます。

 

使用するVBAの主要な命令

ブック集約マクロには、いくつかのVBA命令を使います。

 

主要な命令を事前に確認しておきましょう。

 

これらの命令を組み合わせることで、ブック集約マクロが完成します。

 

サンプルで使うフォルダ構成を確認する

今回は次のようなフォルダ構成を想定してマクロを作成します。

デスクトップに「集約用フォルダ」を作成し、その中に集約対象となるエクセルファイルを複数保存しておきます。

 

マクロが設定されているエクセル（集約先ブック）は、「集約用フォルダ」の外に保存。

マクロを実行すると、「集約用フォルダ」内の全エクセルファイルの全シートが集約先ブックに自動的にコピーされる、という仕組みです。

 

ブック集約VBAの基本コード

それでは、実際のVBAコードを見ていきましょう。

まず基本となるシンプルな集約マクロを紹介し、その後に実務で使えるように機能を追加した応用版を解説します。

 

基本の集約マクロ（全シートコピー）

以下のコードは、指定フォルダ内の全エクセルファイルを開き、全シートを集約先ブックにコピーする基本マクロです。

Sub ブック集約マクロ()

Dim folderPath As String
Dim fileName As String
Dim openBook As Workbook
Dim ws As Worksheet
Dim thisBook As Workbook

'集約先ブック（マクロが設定されているエクセル）を変数に格納
Set thisBook = ThisWorkbook

'集約対象ファイルが保存されているフォルダのパスを指定
folderPath = "C:\Users\Desktop\集約用フォルダ\"

'指定フォルダの最初のエクセルファイルを取得
fileName = Dir(folderPath & "*.xlsx")

'全ファイルをループ処理
Do While fileName <> ""

'集約先ブック自身はスキップ
If fileName <> thisBook.Name Then

'対象ファイルを開く
Set openBook = Workbooks.Open(folderPath & fileName)

'開いたブックの全シートをコピー
For Each ws In openBook.Worksheets
ws.Copy After:=thisBook.Sheets(thisBook.Sheets.Count)
Next ws

'開いたブックを保存せずに閉じる
openBook.Close SaveChanges:=False

End If

'次のファイルに進む
fileName = Dir()

Loop

MsgBox "集約が完了しました。", vbInformation

End Sub

コードの流れを解説します。

 

最初に Set thisBook = ThisWorkbook で、マクロが設定されているエクセル（集約先ブック）を変数に格納しています。

これをしておかないと、後の処理でどのブックに集約しているのかがVBAに伝わりません。

 

folderPath には集約対象ファイルが保存されているフォルダのパスを指定します。

パスの末尾には必ずバックスラッシュ（\）を入れておくのがポイントです。

 

Dir(folderPath & “*.xlsx") は、指定フォルダ内にある拡張子が「.xlsx」のファイルを1件取得します。

「*.xlsx」は「.xlsxで終わる名前のファイルであれば何でも対象にする」という意味で、ワイルドカードと呼ばれる指定方法です。

 

Do While fileName <> “" のループは、フォルダ内にファイルがある限り繰り返す処理です。

ループの最後に fileName = Dir() を書くことで、次のファイル名を取得して処理を続けます。

 

If fileName <> thisBook.Name は、集約先ブック自身を誤って集約対象にしないための条件分岐です。

集約先ブックが同じフォルダに保存されているケースでも安全に動作させることが可能。

 

For Each ws In openBook.Worksheets のループで、開いたブックの全シートを順番に処理します。

ws.Copy After:=thisBook.Sheets(thisBook.Sheets.Count) は、集約先ブックの最後のシートの後ろにコピーする命令です。

これにより、開いたブックのシートが順番に集約先ブックの末尾に追加されていきます。

 

応用版：シート名にファイル名を追加する

基本の集約マクロでは、コピーしたシートの名前がそのまま集約先ブックに追加される仕組みです。

この場合、「シート名が重複してエラーになる」「このシートはどのファイルからコピーしたものかわからなくなる」という問題が発生します。

 

これを解決するのが、コピー後のシート名にファイル名を追加する処理です。

 

Sub ブック集約マクロ_シート名更新()

Dim folderPath As String
Dim fileName As String
Dim openBook As Workbook
Dim ws As Worksheet
Dim thisBook As Workbook
Dim newSheetName As String
Dim baseName As String

Set thisBook = ThisWorkbook
folderPath = "C:\Users\Desktop\集約用フォルダ\"
fileName = Dir(folderPath & "*.xlsx")

Do While fileName <> ""

If fileName <> thisBook.Name Then

Set openBook = Workbooks.Open(folderPath & fileName)

'拡張子を除いたファイル名を取得
baseName = Left(fileName, Len(fileName) - 5)

For Each ws In openBook.Worksheets

ws.Copy After:=thisBook.Sheets(thisBook.Sheets.Count)

'「ファイル名_シート名」という形式でシート名を変更
newSheetName = Left(baseName & "_" & ws.Name, 31)
thisBook.Sheets(thisBook.Sheets.Count).Name = newSheetName

Next ws

openBook.Close SaveChanges:=False

End If

fileName = Dir()

Loop

MsgBox "シート名を更新して集約が完了しました。", vbInformation

End Sub

 

追加したポイントを解説します。

baseName = Left(fileName, Len(fileName) – 5) は、ファイル名から拡張子「.xlsx」の5文字を除いた文字列を取得しています。

たとえばファイル名が「田中_勤務整理.xlsx」の場合、baseName は「田中_勤務整理」になります。

 

newSheetName = Left(baseName & “_" & ws.Name, 31) は、「ファイル名_シート名」という形式で新しいシート名を作成しています。

エクセルのシート名は最大31文字という制限があるため、Left関数で31文字以内に切り詰めています。

この1行がないと、シート名が31文字を超えたときにエラーが発生します。

 

応用版②：フォルダをダイアログで選択できるようにする

フォルダパスをコードに直接書いてしまうと、フォルダ名や保存場所が変わるたびにコードを修正しなければなりません。

フォルダ選択ダイアログを使うと、マクロ実行のたびにフォルダを選ぶことができるため誰でも簡単に操作できるようになります。

 

Sub ブック集約マクロ_フォルダ選択()

Dim folderPath As String
Dim fileName As String
Dim openBook As Workbook
Dim ws As Worksheet
Dim thisBook As Workbook
Dim baseName As String
Dim newSheetName As String

Set thisBook = ThisWorkbook

'「フォルダの選択」ダイアログを表示する',
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "集約するフォルダを選択してください"
If .Show = False Then
MsgBox "キャンセルされました。"
Exit Sub
End If
folderPath = .SelectedItems(1) & "\"
End With

fileName = Dir(folderPath & "*.xlsx")

Do While fileName <> ""

If fileName <> thisBook.Name Then

Set openBook = Workbooks.Open(folderPath & fileName)
baseName = Left(fileName, Len(fileName) - 5)

For Each ws In openBook.Worksheets
ws.Copy After:=thisBook.Sheets(thisBook.Sheets.Count)
newSheetName = Left(baseName & "_" & ws.Name, 31)
thisBook.Sheets(thisBook.Sheets.Count).Name = newSheetName
Next ws

openBook.Close SaveChanges:=False

End If

fileName = Dir()

Loop

MsgBox "集約が完了しました。", vbInformation

End Sub

 

Application.FileDialog(msoFileDialogFolderPicker) は、フォルダを選択するダイアログを開く命令です。

.Show でダイアログを表示し、ユーザーがキャンセルした場合は Exit Sub でマクロを終了する。.SelectedItems(1) で選択されたフォルダのパスを取得し、末尾にバックスラッシュを追加してfolderPathに格納する。という内容です

これで、マクロを実行するたびにフォルダを自由に選択できるようになりました。

 

集約マクロを実務で活用できる3つのシーン

ブック集約マクロは、さまざまな業務に応用できます。

私がマクロ開発を受注した中でも、特に活用事例が多かった3つのシーンを紹介します。

活用シーン①：職員ごとの勤務整理情報を月次で集約する

職員ごとにエクセルファイルで勤務状況を管理している職場では、毎月末に全員分のファイルを開いてデータをとりまとめる作業が発生します。

職員が50名いれば50ファイルを開いてコピペする、という作業が毎月繰り返される。

 

このような作業を手作業で行えば最低でも30分以上の時間がかかりますが、マクロを使えば50ファイルの集約が数分で完了します。

 

担当者の入れ替わりがあっても、マクロの使い方を伝えるだけで同じ作業を引き継ぐことが可能です。

 

活用シーン②：設備ごとの検査データを集計する

工場や施設などで、設備ごとにエクセルファイルで点検記録を管理しているケースも多くあります。

定期点検後に全設備のデータを集約して報告書を作成する、という業務にも集約マクロは有効です。

設備の数が増えるほど手作業の負担も増えますが、マクロなら設備が増えてもボタン1クリックで対応できます。

 

活用シーン③：複数部門の超勤整理簿を一括集約する

部門ごとに超過勤務のデータをエクセルで管理している場合、月次・週次で全部門分を集約する作業が発生します。

部門数が多い場合、集約作業だけで半日かかるというケースも珍しくありません。

 

集約マクロを使えば、全部門のデータを数分で1つのエクセルにまとめることが可能です。

集約後のデータを使って集計や分析を行う場合も、データが1カ所にまとまっているので処理がしやすくなります。

 

ブック集約マクロを使う際の注意点

ブック集約マクロは非常に便利ですが、いくつかの注意点を把握しておかないと、想定外のエラーが発生することがあります。

 

注意点①：集約先ブックのシートが増えすぎないようにする

集約マクロを実行するたびに新しいシートが追加されていくため、実行前に前回の集約シートを削除しておかないとシートが無限に増え続けます。

集約マクロの先頭に「集約済みのシートを削除する処理」を追加しておくか、毎回集約先ブックを新規作成するような仕組みにしておきましょう。

 

注意点②：ファイルが開いていると処理がエラーになる

集約対象のファイルが他のユーザーによってすでに開かれている場合、Workbooks.Open がエラーになることがあります。

複数人で共有フォルダを使っている場合は、集約作業を行う前に対象ファイルが全て閉じられているかを確認するようにしてください。

On Error Resume Next を使ったエラー処理を組み込むと、開けなかったファイルをスキップして処理を続けることも可能です。

 

注意点③：ファイルパスに日本語が含まれる場合の動作確認

フォルダ名やファイル名に日本語（全角文字）が含まれている場合、環境によっては Dir関数 や Workbooks.Open が正しく動作しないことがあります。

フォルダ名やファイル名はできるだけ半角英数字を使うか、実際の環境で動作テストを行ってから本番運用に移行するようにしましょう。

 

ブック集約マクロをボタンに登録して使いやすくする方法

ここまで紹介したVBAコードは、ボタンに登録することで誰でも簡単に使えるようになります。

毎回「開発」タブからマクロを呼び出す必要がなくなり、操作ミスも防げます。

 

ボタンへの登録手順

「開発」タブ → 「挿入」→「ボタン（フォームコントロール）」を選択します。

 

シート上でドラッグしてボタンを描画すると、「マクロの登録」ダイアログが表示されます。

 

登録したいマクロ名（例：ブック集約マクロ_フォルダ選択）を選んで「OK」をクリックすれば設定完了です。

ボタン上で右クリック→「テキストの編集」を選ぶと、「▶ ブック集約を実行」などのわかりやすい名前をボタンに表示できます。

 

一度ボタンを設置してしまえば、次回からはボタンをクリックするだけでフォルダ選択ダイアログが開き、集約処理が自動で実行されます。

マクロを知らない同僚でも、使い方を教えれば誰でも使いこなせるようになります。

 

まとめ ～ ブックの操作はVBAで自動化しよう ～

エクセルの集約作業は、手作業で行うとファイル数が増えるほど時間もミスも増えます。

ブック集約マクロを使えば、フォルダを指定するだけで全ファイルの全シートを自動集約することが可能。

 

勤務整理情報の集約、検査データの集計、超勤整理簿のとりまとめなど、あらゆるエクセル集約業務に応用できる汎用性の高いマクロです。

今回紹介した3つのパターン（基本版、シート名更新版、フォルダ選択版）をベースに、自分の業務に合わせてカスタマイズしてみてください。

 

VBAコードが設定されたエクセルファイルをそのままダウンロードしたい方は、関連記事「バラバラに保存された大量エクセルファイルをボタン1クリックで集約する方法とは」でダウンロードできます。

 

 

もし「自分の業務に合わせた複雑なチェック処理を実装したい」「既存のマクロを改良したい」といったご要望がありましたら、マメBlogのエクセルマクロ開発依頼にご相談ください。

 

 

業務内容に合わせたオーダーメイドマクロを設定させていただきます。

 

最後まで読んでいただきありがとうございました。

エクセルVBAを使って面倒なルーティーン作業を自動化しちゃいましょう。