This is the code i am using right now.
Code:
'Loop through each file in the folder
For Each objFile In objFiles
If InStr(1, objFile.Path, ".xls") > 0 Then
Set wbTarget = Workbooks.Open(objFile.Path)
With wbTarget.Worksheets(1)
ary(0) = objFile.Path
End With
With wbMaster.Worksheets(1)
lColumn = .Range("A1").End(xlToRight).Offset(0, 1).Column
End With
wbTarget.Worksheets(1).Range("I2:I255").Copy
wbMaster.Worksheets(1).Cells(1, lColumn) = Right(ary(0), 15)
wbMaster.Worksheets(1).Cells(2, lColumn).PasteSpecial Paste:=xlPasteAll
wbTarget.Close savechanges:=False
End If
Next objFile
Bookmarks