Merge Multiple files into single worksheet

[PivotMeThis]

I've been searching the board and experimenting with code but I can't find what I need. I have 6 workbooks that have all the same worksheets and column headings. They have been split up from a single file so that specific users can use them without the files being locked by someone else when they need to use them. They are xlsm files and I couldn't 'share' the single file without breaking the code.

Part of this code always opens the file to a specific worksheet (ProjectInfo) so that the user does not inadvertently fill out the wrong sheet. Another part of the code copies specific lines from this sheet to another sheet called MAP. There are also a couple of hidden sheets but they don't seem to be a problem.

I need to merge the data from the MAP sheet from these 6 files into one file. The code I have seen says it must be the first sheet but MAP has always been the last. I moved it to the beginning but that didn't help because (I think) the file always opens to the ProjectInfo tab and it's pasting that information instead of what is on the MAP tab.

This is the latest code that I've been playing with.

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("W:\Highway\Construction\Staff\Senior Engineering Technician - Rhonda\TEST")
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("A2: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(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

Is there a way to customize this (or better code) that will use the information on the MAP tab and paste it?

Thanks!

 

[PivotMeThis]

Scratch this - I now need to merge two worksheets in this file. I am going to resume my search for different code.