Excel VBA マクロ 特定列のコピーを別ブックにペーストを繰り返す方法

Question

エクセルのファイルが日別にあり、そのすべてのブックから、３１あるシートの中の商品シートの一部である二列（AJ1:AK500）の値をコピーして
集計というエクセルファイルに左詰めでペーストしていきたいと思っています。

' Macro1 Macro
'
'
Sub Merge()

Dim MergeBook As Workbook
Dim CurrentBook As Workbook
Dim CurrrentPath As String
Dim Filename As String
Dim n As Integer

Application.ScreenUpdating = False
Set MergeBook = ThisWorkbook
CurrentPath = MergeBook.Path
Filename = Dir(CurrentPath & "*.xls")

n = 0
Do While Filename <> Empty
    If Filename <> MergeBook.Name Then
        Set CurrentBook = Workbooks.Open(CurrentPath & "\" & Filename)
        CurrentBook.Worksheets.Copy after:=MergeBook.Sheets(MergeBook.Sheets.Count)
        CurrentBook.Close
        n = n + 1
    End If
    Filename = Dir
Loop

Application.ScreenUpdating = True
MsgBox n & "件のブックを処理しました。"

End Sub

'各シートのAJ1:AK500を末尾の集計シートに貼り付ける
Sub CreatNewSheet()
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "集計"
End Sub

Sub NameCopy()
Dim i As Long

For i = 2 To Sheets.Count
    Sheets(商品).Range("AJ1:AK500").Copy Sheets("集計").Cells(i, 1)
Next

End Sub

まったくの初心者でいろいろ検索しながらやったのですがうまくいきません。
何かアドバイスいただけないでしょうか？

Answer 1

こんな感じでどうでしょうか。

Sub Merge()
    
    Dim MergeBook As Workbook
    Dim CurrentBook As Workbook
    Dim CurrentPath As String
    Dim Filename As String
    Dim n As Integer
    
    Application.ScreenUpdating = False
    Set MergeBook = ThisWorkbook
    
    Dim MergeSheet As Worksheet
    Set MergeSheet = MergeBook.Worksheets.Add(, MergeBook.Worksheets.Count)
    MergeSheet.Name = "集計"
    
    CurrentPath = MergeBook.Path
    Filename = Dir(CurrentPath & "*.xls?")
    
    n = 0
    Do While Filename <> Empty
        If Filename <> MergeBook.Name Then
            Set CurrentBook = Workbooks.Open(CurrentPath & "\" & Filename)
            Dim ws As Worksheet
            For Each ws In CurrentBook.Worksheets
                ws.Range("AJ1:AK500").Copy MergeSheet.Range("A" & MergeSheet.Rows.Count).End(xlUp).Offset(1)
            Next
            CurrentBook.Close False
            n = n + 1
        End If
        Filename = Dir
    Loop
    
    Application.ScreenUpdating = True
    MsgBox n & "件のブックを処理しました。"
End Sub

Comments

[minakosun]

ありがとうございます！
Set MergeSheet = MergeBook.Worksheets.Add
の部分でエラーが出てしまい、調べたところ指定がいらないそうなので削除したら動きました！
まだ、値でのコピーの部分（コピーの処理をどこで行っているのか）
と改行がうまく動作していないのでそちらの原因を自分なりにも調べております。

お手数ですが何か解決策はご存じではないでしょうか？