Search code examples
excel-2013

How to merge multiple excel spread sheet in to single spread sheet

I have 20-22 separate spread sheets and I want to merge it in to single spreadsheet. Is there any way to do it?

Solution

  • To merge it in to single spreadsheet use the following VB code First consolidate all the spreadsheet in to single folder

    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("Folder path (Consolidated spreadsheet folder path")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

    change "A1" with cell reference of start point for every files here 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

Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub