lyyynnnchy
New Member
- Joined
- Sep 10, 2014
- Messages
- 5
Hi All, First time poster This forum is a lifesaver!
I'm trying to merge multiple excel files in the same folder in to 1 blank workbook. All files in the directory need to be merged and all worksheets in each file need to be transferred across.
At the moment I'm trying to use
But it comes up with the error
Run-time error '9':
Subscript out of range
I'm trying to merge multiple excel files in the same folder in to 1 blank workbook. All files in the directory need to be merged and all worksheets in each file need to be transferred across.
At the moment I'm trying to use
Code:
Sub MergeSheets() Dim SrcBook As Workbook
Dim fso As Object, f As Object, ff As Object, f1 As Object
Application.ScreenUpdating = False
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.Getfolder("[I][B]My Path[/B][/I]")
Set ff = f.Files
For Each f1 In ff
Set SrcBook = Workbooks.Open(f1)
Sheets("Data").Select
Range("A1:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
SrcBook.Close
Next
End Sub
But it comes up with the error
Run-time error '9':
Subscript out of range
Last edited: