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行目指定とします。

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

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

VBAを実装する

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

VBAを実行する

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

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

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

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

        'コピー元ブック一覧があるシートの３行目からパスを取得します。
        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の設定について説明をします。

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

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

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

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

VBAを実行する

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

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

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

VBAの説明

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

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

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

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

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

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

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

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

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

範囲選択を解除します。

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

VBAの実装手順

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

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

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

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

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

こちらで完了です。

VBAを実行する

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

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

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

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

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

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

■変更前

■変更後

さいごに

いかがでしょうか。

今回は、

についてまとめました。

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