【VBA】開いている全ブックを開かずに(外部参照で)転記する方法

毎回開くのが面倒な大量のExcelブックからデータを抽出してまとめたい！そんな悩みを解決する方法を紹介します。この記事では、他のExcelファイルに保存されているデータを参照し、現在開いているワークブックへ転記するVBAマクロを作成します。

サンプルコード

VBA

Option Explicit
Sub TransferDataFromOtherBooks()
    Dim wb As Workbook, ws As Worksheet
    Dim sourceWb As Workbook, sourceWs As Worksheet
    Dim lastRow As Long, i As Long
    
    Application.ScreenUpdating = False '画面更新を停止する
    Set ws = ThisWorkbook.Sheets("Sheet1") '転記先のワークシート
    
    For Each wb In Workbooks '開いている全ブックに対してループ
        If wb.Name <> ThisWorkbook.Name Then '現在のワークブック以外の場合
            Set sourceWb = Workbooks(wb.Name) '参照するワークブックを設定
            Set sourceWs = sourceWb.Sheets("Sheet1") '参照するワークシートを設定
            lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1 '転記先の最終行を取得
            
            For i = 1 To sourceWs.UsedRange.Rows.Count '元データの行数分ループ
                ws.Cells(lastRow + i - 1, 1) = sourceWs.Cells(i, 1).Value 'A列の値を転記
                ws.Cells(lastRow + i - 1, 2) = sourceWs.Cells(i, 2).Value 'B列の値を転記
            Next i
        End If
    Next wb
    
    Application.ScreenUpdating = True '画面更新を再開する
End Sub

よくある質問

Q 元に戻せますか？

VBAの実行結果は「元に戻す」が効きません。必ずバックアップを取ってから実行してください。

Q エラーが出たら？

シート名や列番号が正しいか確認してください。