Copying multiple files to 1 new workbook

[thorwood]

Hi
I'm new to VBA macro scripting in Excel. However, I have the below code, which I need help with.

The idea is it opens a number of files from my Y:/Scripts4/ folder names 1.xls, 2.xls, 3.xls, 4.xls etc and, for each one it opens, it copies cell range B10:O29 to a new file, I've just called "collate_new.xls"

Everything works, except the line in bold, so I assume I have this wrong. Can anyone help?

Thanks
Tim

p.s also, I want the data to be pasted into collate_new, and then the next set of data to be pasted after it etc, not overwritten. Not sure if the code below allows me to do that, but that's the aim.

Sub collate_new()

Dim sFile As String
Dim arFiles(100) As String
Dim i As Integer, j As Integer

sFile = Dir$("Y:\Scripts4\*.xls", vbNormal)
    i = 0
    
' Fills array
    Do While sFile <> ""
      arFiles(i) = sFile
      sFile = Dir$()
       i = i + 1
    Loop
' Loop opens, copys and pastes
    For j = 0 To i - 1
        Workbooks.Open Filename:=arFiles(j)
        Closerfile = arFiles(j)
    Range("B10:O29").Copy
         Windows("Collate_new").Activate
    ActiveSheet.Paste
         Windows(arFiles(j)).Activate
        ActiveSheet.Close
  
         
    Next

End Sub