複数のブックを１ブックに集約

Module1.bas

Const dirPath As String = "DATA*.csv"
Const rowCopyStart As Long = 5 'コピー開始行

Dim wbParent As Workbook '親エクセル貼り付け用
Dim rowPasted As Long    '次回貼り付け開始行

Sub ボタン1_Click()

    '親エクセルを変数に保存
    Set wbParent = ThisWorkbook
    rowPasted = 1

    Dim buf As String, cnt As Long
    Dim Path As String
    Path = ThisWorkbook.Path & "\"
    ChDir ThisWorkbook.Path
    buf = Dir(Path & dirPath) '''''''''''''''''''''''''データ取得対象ファイル
    
    Do While buf <> ""
    
        'Close時の警告表示を一時的に非表示
        Workbooks.OpenText Filename:=buf
        
        Call DataCopyPaste
        
        'Workbook Close
        Application.DisplayAlerts = False
        Workbooks(buf).Close
        Application.DisplayAlerts = True
        
        cnt = cnt + 1
        buf = Dir()
    Loop
    
    Range("A1").Select
    
    
End Sub
Sub DataCopyPaste()

    Dim rowEnd As Long

    'データの最下行を検索
    Range("A1000").Select
    Selection.End(xlUp).Select
    rowEnd = ActiveCell.Row
    
    'コピー
    Rows(rowCopyStart & ":" & rowEnd).Select
    Selection.Copy
    
    '貼り付け
    wbParent.Activate
    ActiveWorkbook.Sheets("out").Select
    Cells(rowPasted + 1, 1).Select
    ActiveSheet.Paste
    
    '次回貼り付け開始行計算
    Range("A1000").Select
    Selection.End(xlUp).Select
    rowPasted = ActiveCell.Row
    
End Sub