Excel VBAで複数ブックのデータを1シートにまとめる方法

Excel VBAで複数ブックのシートを1シートにまとめる方法!フォルダ内ブックも一括処理!

Excel VBAで複数ブックのシートデータを1シートにまとめたいときはないでしょうか。

けど、そんな中で悩むことは、

・Excel VBAで複数ブックのシートデータを1シートにまとめたいが方法がよくわからない
・Excel VBAでフォルダにある複数ブックのリストを取得した上でデータを集約したいが方法がよくわからない

ですよね。

今回はそんなお悩みを解決する

・Excel VBAで複数ブックのシートを1シートにまとめる方法
・Excel VBAでフォルダにある複数ブックのリストを取得し、各データを1シートにまとめる方法

についてまとめます!

Excel VBAで複数ブックのシートデータを1シートにまとめるイメージ

Excel VBAで複数ブックのシートデータを1シートにまとめるイメージについて説明をします。

特定シートにデータが入力されている複数のExcelブックを用意します。

次にデータをまとめる先となるExcelブックを用意します。

VBAを実行すると

まず、フォルダにあるブックのリストが収集されます。

次にVBAを実行すると、リストに沿ってコピー元のブックが開かれ、

データまとめ先のExcelブックのシートへコピーされていきます。

この処理をフォルダにあるExcelブック分繰り返して、完成となります。

タカヒロ
タカヒロ
毎月のデータがあるブックを四半期や年次でまとめなければならない場合にとても重宝するかと思います。

データの準備

まずはデータの準備をしましょう。
特定シートにデータが入力されている複数のExcelブックを用意し、

フォルダに格納します。

Excel VBAでフォルダにある複数ブックのリストを取得

Excel VBAでフォルダにある複数ブックのリストを取得していきましょう。

まとめ用ブックを用意する

まとめ用ブックを用意します。シート名は「コピー元ブック一覧」とします。

またコピー先のシートも用意します。シート名は「別ブックデータ集約」とします。

VBAコード

VBAコードは以下の通りです。

Sub フォルダ内のファイル名やファイルパスを取得()
    
    Dim intIchi As Integer
    Dim strFolderpass As String
    Dim objfFSO As Object
    Dim objFiles As Object
    Dim objFile As Object
    
    
    'ファイル名一覧を出力する行番号を指定します。
    intIchi = 3
    
    '対象フォルダのファイルパスがあるセルを指定し、値を代入します。
    strFolderpass = Range("B1").Value
    
    '指定範囲の値をクリアします。
    Range("A3:B10000").Clear
    
    
    '対象フォルダのファイルパスの値があれば処理をおこないます。
    If strFolderpass = "" Then
        MsgBox "対象フォルダのファイルパスがありません。ファイルパスを入力してください。"
    Else
    
        'FileSystemObjectはファイルやフォルダを操作する専用のオブジェクトです。インスタンスにセットします。
        Set objfFSO = CreateObject("Scripting.FileSystemObject")
    
        '対象フォルダのファイルオブジェクトをセットします。
        Set objFiles = objfFSO.GetFolder(strFolderpass).Files
        
        'ファイル情報を出力します。
        For Each objFile In objFiles
            Cells(intIchi, 1) = objFile.Name
            Cells(intIchi, 2) = objFile.Path
            intIchi = intIchi + 1
        Next objFile
    
    End If
    
    Set objfFSO = Nothing
    Set objFiles = Nothing
    Set objFile = Nothing

End Sub

VBAの設定をする

VBAの設定について説明をします。

ファイル名一覧を出力する行番号を指定します。
今回は3行目指定とします。

intIchi = 3

対象フォルダのファイルパスがあるセルを指定し、値を代入します。
B1セル以外にパスを記載したい場合は変更をしてください。

strFolderpass = Range("B1").Value

指定範囲の値をクリアします。
行数が1万件以上ある場合は変更をしてください。1万件以内であればそのままで結構です。

Range("A3:B10000").Clear

VBAを実装する

VBAの実装方法については
VBAの実装手順
をご参照ください。

VBAを実行する

B1セルにブックが格納されているフォルダのパスを記載します。

VBAを実行してみましょう。
「フォルダ内のファイル名やファイルパスを取得」を実行します。

はい!3行目以降にブックのブック名とパスが出力されましたね!

VBAの説明

VBAの内容については以下記事をご参照ください。

【Excel VBA】フォルダ内のフォルダ名を取得する方法!サブフォルダ名も取得可!

Excel VBAで複数ブックのシートデータを1シートにまとめる

Excel VBAで複数ブックのシートデータを1シートにまとめていきましょう。

VBAコード

VBAコードは以下の通りです。

Sub 各ブックのシートのデータをまとめ用ブックへコピーする()

    Dim objWorkbook As Workbook
    Dim i As Integer
    Dim strBookList As Variant
    Dim intLastRowNum As Integer
    Dim intLastRowNum2 As Integer
    Dim intLastRowNum3 As Integer
    Dim strCopyMotoListSheetName As String
    Dim strCopyMotoSheetName As String
    Dim strCopySakiSheetName As String
    
    'コピー元ブック一覧があるシート名を指定します。
    strCopyMotoListSheetName = "コピー元ブック一覧"
    
    'コピー元ブックのシート名を指定します。
    strCopyMotoSheetName = "Sheet1"

    'コピー先のシート名を指定します。
    strCopySakiSheetName = "別ブックデータ集約"

    With ThisWorkbook
        'B列を対象に最終行数を取得します。
        intLastRowNum = .Sheets(strCopyMotoListSheetName).Cells(Rows.Count, 2).End(xlUp).Row

        'コピー元ブック一覧があるシートの3行目からパスを取得します。
        For i = 3 To intLastRowNum
            
            'コピー元ブックパスを取得します。
            strBookList = .Sheets(strCopyMotoListSheetName).Cells(i, 2).Value
            
            'コピー元ブックを開きます。
            Set objWorkbook = Workbooks.Open(strBookList)
            
            With objWorkbook.Sheets(strCopyMotoSheetName)
                'コピー元ブックのA列を対象に最終行数を取得します。
                intLastRowNum2 = .Cells(Rows.Count, 1).End(xlUp).Row
                
                'コピー元ブックのセル範囲をコピーします。コピーの範囲を変更する場合は設定を変えてください。
                .Range("A2:C" & intLastRowNum2).Copy
                
            End With
            
            'コピー先のシートA列を対象に最終行数を取得し、1加算します。
            intLastRowNum3 = .Sheets(strCopySakiSheetName).Cells(Rows.Count, 1).End(xlUp).Row + 1
    
            'コピー先のシートへ値のみ貼り付けします。貼り付け開始列はA列となります。
            .Sheets(strCopySakiSheetName).Cells(intLastRowNum3, 1).PasteSpecial Paste:=xlPasteValues
            
        Next i
        
    End With


    '範囲選択を解除します。
    Application.CutCopyMode = False
    
    'オブジェクトを解放します。
    Set objWorkbook = Nothing
    
  
End Sub

VBAの設定をする

VBAの設定について説明をします。

コピー元ブック一覧があるシート名を指定します。

strCopyMotoListSheetName = "コピー元ブック一覧"

コピー元ブックのシート名を指定します。

strCopyMotoSheetName = "Sheet1"

コピー先のシート名を指定します。

strCopySakiSheetName = "別ブックデータ集約"

コピー元ブックのセル範囲をコピーします。コピーの範囲を変更する場合は設定を変えてください。

.Range("A2:C" & intLastRowNum2).Copy

VBAを実行する

VBAを実行してみましょう。

「各ブックのシートのデータをまとめ用ブックへコピーする」を実行します。

はい!「別ブックデータ集約」シートに各ブックの指定シートにあるデータが集約されましたね!

VBAの説明

VBAの内容について説明をします。

B列を対象に最終行数を取得します。

intLastRowNum = .Sheets(strCopyMotoListSheetName).Cells(Rows.Count, 2).End(xlUp).Row

コピー元ブック一覧があるシートの3行目からパスを取得し最終行数分繰り返します。

For i = 3 To intLastRowNum Next i

コピー元ブックパスを取得します。

strBookList = .Sheets(strCopyMotoListSheetName).Cells(i, 2).Value

コピー元ブックを開きます。

Set objWorkbook = Workbooks.Open(strBookList)

コピー元ブックのA列を対象に最終行数を取得します。

intLastRowNum2 = .Cells(Rows.Count, 1).End(xlUp).Row

コピー元ブックのセル範囲をコピーします。コピーの範囲を変更する場合は設定を変えてください。

.Range("A2:C" & intLastRowNum2).Copy

コピー先のシートA列を対象に最終行数を取得し、1加算します。

intLastRowNum3 = .Sheets(strCopySakiSheetName).Cells(Rows.Count, 1).End(xlUp).Row + 1

コピー先のシートへ値のみ貼り付けします。貼り付け開始列はA列となります。

.Sheets(strCopySakiSheetName).Cells(intLastRowNum3, 1).PasteSpecial Paste:=xlPasteValues

範囲選択を解除します。

Application.CutCopyMode = False

オブジェクトを解放します。

Set objWorkbook = Nothing

VBAの実装手順

実装手順は以下の通りです。

Excel側にVBAを実装していきます。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」+「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。

<追加>コピー元のブックをコピー後に閉じる方法

コピー元のブックをコピー後に閉じる方法について追記します。

データをコピーし終えた後にコピー元のブックを閉じる処理を追加します。
具体的には以下のようにワークブックオブジェクトにCloseメソッドを付け加えていきます。

■変更前

'範囲選択を解除します。
Application.CutCopyMode = False

■変更後

'範囲選択を解除します。
Application.CutCopyMode = False

'オブジェクトを閉じます。
objWorkbook.Close

さいごに

いかがでしょうか。

今回は、

・Excel VBAで複数ブックのシートを1シートにまとめる方法
・Excel VBAでフォルダにある複数ブックのリストを取得し、各データを1シートにまとめる方法

についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。









6 件のコメント

  • とても便利だと思い参考にさせていただいております。
    最後のオブジェクトを開放した後にコピー元のブックをすべて閉じるのを追加したいのですがうまく出来ません。
    VBA初心者のため教えていただけると幸いです。よろしくお願いいたします。

  • 迅速なご対応ありがとうございました。

    大変業務で参考になる記事を書いていただいており感謝申し上げます。

    記事投稿大変かと思いますが、
    これからも更新楽しみにしております。

    • ご指摘ありがとうございます。

      またコードの内容が誤っていたことお詫び申し上げます。

      先ほど修正をさせていただきました。

      よろしくお願いいたします。

  • コメントを残す

    メールアドレスが公開されることはありません。 * が付いている欄は必須項目です