CODEWITHSUNDEEP
vbaexcel
Demo
Demo

Reputation: 3

I want to merge data from multiple excel workbooks in a folder into single excel sheet

I have to pick single row (row 9) data (out of 5 rows (row 6-10) of data in each file) from multiple excel files. all these files are in single folder and the consolidation file is one level up in file structure. Below given code copies 4 (row 6-9) rows. Please help:

Code being used is given below:

Sub Auto_Open()
    'MsgBox "Welcome to ANALYSIS TABS"
    'End Sub

    'Sub simpleXlsMerger()
    Dim bookList As Workbook
    Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object

    MsgBox "Welcome to Merging Platform..."

    Application.ScreenUpdating = True
    Set mergeObj = CreateObject("Scripting.FileSystemObject")

    'change folder path of excel files here
    Set dirObj = mergeObj.Getfolder("C:\Users\00508069\Desktop\New folder\data")
    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("A9:IV" & Range("A10").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

Upvotes: 0

Views: 125

Answers (2)

ASH
ASH

Reputation: 20302

This should do what you want.

https://www.rondebruin.nl/win/addins/rdbmerge.htm

enter image description here

Upvotes: 0

Egan Wolf
Egan Wolf

Reputation: 3573

The problem is in this line

Range("A9:IV" & Range("A10").End(xlUp).Row).Copy

more strictly in this part

Range("A10").End(xlUp).Row

which says "From cell A10 go up to the cell where block of data ends and take row number of this cell". In your case it means 6.

Use this instead and you'll be fine

Range("A9:IV9").Copy

Upvotes: 1

Related Questions