Copying multiple files to 1 new workbook

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

Please take the time to read the Forum Rules & use Code Tags in future.

If you check the earlier posts, I posted code to do this only yesterday.

here's a link

http://www.excelforum.com/excel-prog...n-of-data.html

Something like this could work

ActiveSheet.Range("B10:O29").Copy Destination:=Workbooks("Collate_new").Sheets("Sheet1").Range("AA65536").End(xlUp).Offset(1, 0)

This will paste to the first blank cell in column A(May have to change the sheet name)
Look at this post for an example
http://www.excelforum.com/excel-prog...-into-one.html