Import Multiple Excel Files From Folder Into One Excel Sheet (Urdu/Hindi)

Опубликовано: 04 Май 2020
на канале: Excel Advisor

8,344

199

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("C:\Users\waleed.butt\Desktop\Compile")
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 "A2:IV" to merge all files start from columns A and rows 2
'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("A1000000").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

________________________________________________________________________________________
We might need to combine multiple workbooks into one workbook in order to analyse or present the information better. Though this can be done manually, by moving or copying all the worksheets from different workbooks into one workbook, but it would be time consuming and prone to errors. Moreover if there are a large number of workbooks/worksheets, then the problem is even bigger.

However, we have a solution to this and it would not take more than a few seconds to get all workbooks merged into one. Here is the VBA code that can help us achieve this. Even those who are not well versed with VBA can easily get this done by simply following the video on how to use this code.

Merge Two or More Excel Workbooks into One