Consolidating Workbooks with two sheets into One workbook with the same sheet. Second sheet will not copy correctly

Question

I am trying to copy data from 3 separate workbooks into one workbook with two sheets. All the workbooks have the same set-up just different amounts of data. When I run my code, the second sheet "All Insured" only has the same amount as the coverage employee. It is as if the EndxlUp is not working when copying from the 3 seperate workbooks

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("M:\Active Clients\HBS Inc Clients\NBP - National Beef Packing\Enrollments\Selerix combination\Copy Files Here")
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
Sheets("Coverage by Employee").Range("A2:AR" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets("Coverage by Employee").Activate

'Do not change the following column. It's not the same column as above
Sheets("Coverage by Employee").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next

Dim ibookList As Workbook
Dim imergeObj As Object, idirObj As Object, ifilesObj As Object, ieveryObj As Object

Set imergeObj = CreateObject("Scripting.FileSystemObject")

'change folder path of excel files here
Set idirObj = imergeObj.Getfolder("M:\Active Clients\HBS Inc Clients\NBP - National Beef Packing\Enrollments\Selerix combination\Copy Files Here")
Set ifilesObj = idirObj.Files
For Each ieveryObj In ifilesObj
Set ibookList = 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
Sheets("All Insureds").Range("A2:AV" & Range("A65536").End(xlUp).Row).Copy
Workbooks("Selerix Combination Macro.xlsm").Worksheets("All Insureds").Activate

'Do not change the following column. It's not the same column as above
Workbooks("Selerix Combination Macro.xlsm").Sheets("All Insureds").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
ibookList.Close
Next



End Sub

score 0 · Answer 1 · answered Jan 03 '20 at 20:26

You need to qualify what Worksheet and Workbook every range is on:

Sheets("All Insureds").Range("A2:AV" & Range("A65536").End(xlUp).Row).Copy

should be something like this:

With ibookList.Sheets("All Insureds")
    Dim lastRow as Long
    lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

    .Range("A2:AV" & lastRow).Copy
End With

Consolidating Workbooks with two sheets into One workbook with the same sheet. Second sheet will not copy correctly

1 Answers1