Macro to insert a number into cell A11 for 180+ excel files?

Jax - Oct 6, 2016 at 08:22 PM
I used the macro below to merge a bunch of files but it cut off the last row of data. It looks for the first A cell with nothing in it to end it and that is the data I needed to merge. Basically it cut off the last row of data in all the sheets. I need a way to insert or modify the macro below.

Sub simpleXlsMerger()
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")

'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("\\********\*********$\Desktop\Merge")
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("A1:IV" & Range("A65536").End(xlUp).Row).Copy

'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(2, 0).PasteSpecial
Application.CutCopyMode = False
End Sub