This is my macro for merging all the files in one directory to one excel sheet. I am unable to find why its not working as per expectation. I have thousand rows in each file. It copies 1000 rows from first file but in next file it copies from 1001th row instead of 1st row. in same way 3rd file copies from 2001th row. I want to copy first 1000 rows every while. Which part of code causing problem
Sub MergeFiles()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
Dim i
i = 0
'change folder path of excel files here
Dim TTFiles_Path As String
ThisWorkbook.Worksheets(7).Activate
TTFiles_Path = Range("B2").Value
Set dirObj = mergeObj.GetFolder(TTFiles_Path)
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A4:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(8).Activate
'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub
Range("A65536").End(xlUp).Offset(1, 0)
(two places) to accomodate for basic offset/non-overlap. You might not needi
.