Macro to insert a number into cell A11 for 180+ excel files? [Solved/Closed]

Jax - Oct 6, 2016 at 08:22 PM - Latest reply: Ambucias 52520 Posts Monday February 1, 2010Registration dateModeratorStatus June 18, 2018 Last seen
- Oct 7, 2016 at 05:12 AM
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
ThisWorkbook.Worksheets(1).Activate

'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
bookList.Close
Next
End Sub
See more 

1 reply

Ambucias 52520 Posts Monday February 1, 2010Registration dateModeratorStatus June 18, 2018 Last seen - Oct 7, 2016 at 05:12 AM
0
Thank you
Greetings,

Please, bare in mind that CCM is a mutual aid community, all answers on the forum are provided by volunteers who give their time free to help solve issues.

Hence we request from CCM forum users to show mutual respect. Therefore, when asking assistance, please be courteous, as for elsewhere in similar circumstances in every day society when asking for something. (Usually, how would someone ask for help? i.e. please, thank you, appreciate, grateful, etc.)

Being courteous will also increase your chances of getting the help you need.

http://ccm.net/contents/948-terms-of-use-for-ccm-respect-for-others

Please, copy, modify and repost your message respecting the politeness charter.

We trust that you understand.

Moderator