Excel

別ブック(複数)のデータを別ブックのシートにまとめる・配列

2021-11-16

別ブックのエクセルデータを配列でひとつにまとめる方法です。

まとめたいデータと転記先

転記もとのデータ3ブックと転記先のブックの4ブックの例です。

転記もとのデータ(まとめたいデータ)

データ保存先:F:¥sample

データ保存先

転記もとのデータです。形式は全て同じです。

sample1.xlsx
sample2.xlsx
sample3.xlsx

転記先のブック

シート設定に転記もとのエクセルブック名を入力します。

シート「設定」

シート「書出」に転記もとの見出し以外のデータを転記します。

書き出し前シートです。

シート「書出」

書き出し後です。

シート「書出」

サンプルコード

転記先のブックをアクティブにして実行します。

Sub sample()
    Dim fPath As String: fPath = "F:\sample\"
    Dim fName As String
    Dim wsS As Worksheet, wsW As Worksheet
    Set wsS = Worksheets("設定")
    Set wsW = Worksheets("書出")
    Dim ary As Variant
    Dim i As Long, sRow As Long
    
    sRow = 2
    For i = 2 To 4
         fName = wsS.Cells(i, 1)
         Workbooks.Open Filename:=fPath & fName
         With ActiveWorkbook
            ary = .Worksheets(1).Cells(1, 1).CurrentRegion _
                  .Offset(1).Resize(Cells(1, 1).CurrentRegion.Rows.Count - 1)
            .Close
         End With
         wsW.Cells(sRow, 1).Resize(UBound(ary, 1), UBound(ary, 2)).Value = ary
         sRow = sRow + UBound(ary)
    Next i
End Sub
  • 10行目:転記もとデータのスタート行を指定します。2行目~書き出し。
  • 11行目、21行目:For~Nextでシート「設定」の2行目から4行目に記載のファイルに対して処理します。
  • 15~16行目:転記もとの見出し以外(2行目以降)のデータを配列(ary)に格納します。
  • 18行目:転記もとのエクセルブックを閉じます。
  • 19行目:転記先へデータを貼り付けます。
  • 20行目:次の転記もとデータのスタート行を指定します。

19行目の「wsW.Cells(sRow, 1).Resize(UBound(ary, 1), UBound(ary, 2)).Value = ary」をもう少し詳しく説明します。

参考:Rangeクラス・Resizeプロパティ/セル範囲の変更

以下は1つ目のブックの処理で変数なしで指定した場合です。

Range("A2").Resize(4, 4)

Range("A2")/cells(2,1)の変数です。

  • Cells(sRow, 1)

Resize(4, 4)/(行、列)の変数です。

  • UBound(ary, 1):行サイズ
  • UBound(ary, 2):列サイズ

-Excel
-

© 2021 オフィスのQ&A