Merge all excel sheet data into one

Hi Friend,

Lets see how can we merge the data from all the excel sheet into one.  Lets consider we have one
excel file containing 4-5 excel sheet and all the excel sheet have some data and we want to merge
all the data into one excel sheet so that we can use that result excel sheet instead referring all 4-5
sheets.

You may find few Excel add-ins those are paid, So here I came with zero cost solution.  I used the VBA to merge the sheet.  To make faster I applied a condition so it will not copy the row found blank and immediately script will switch to the another sheet to copy.

Consider we have

Sheet 1 sheet1 data1 sheet1 data2 sheet1 data3		Sheet 2 sheet2 data1 sheet2 data2 sheet2 data3
Sheet 3 sheet3 data1 sheet3 data2 sheet3 data3		Sheet 4 sheet4 data1 data1 data1 sheet4 data2 data2 data2 sheet4 data3 data3 data3 sheet4 data4 data4 data4

if we try to merge it we will get a new sheet named as 'Result' which will have the data like -

Result

sheet1	data1
	sheet1		data2
		sheet2	data1
sheet2	data2
sheet2		data3
sheet4	data1	data1	data1
sheet4	data2	data2	data2
sheet4	data3	data3	data3
sheet4	data4	data4	data4

Notice 4th line from sheet 1 not copied because line no 3 is blank similar for other sheets.  Sheet 3 not got copied because first line is empty.


Now lets see the VBA Code open you excel file

1. Press Alt + F8 to open the Macro prompt

2. Provide the macro name in Text Field and click the Create button

3. It will open the editor to write the code.

4. Remove these two lines and paste the following code

Sub Merge()   

    MSG1 = MsgBox("Merge all sheet and create result sheet", vbYesNo, "Mind It")

    If MSG1 = vbYes Then

        'create the first worksheet for the result

        Call CreateNewResultSheet(Sheets)           

        'call the function to append the other sheet data to the first one (named as result)

        Call AppendData(Sheets)

    Else

        MsgBox "Bbye..!!!"

    End If       

End Sub

---

Private Function CreateNewResultSheet(ws As Sheets)

    Dim i As Integer   

    ' removing first sheet if name is result 
    ' this can be old result sheet

    If ws(1).Name = "Result" Then

        Application.DisplayAlerts = False

        ws(1).Delete

        Application.DisplayAlerts = True

    End If

    ' now checking if any data sheet name is result then
    ' rename it append the date and time in name  

    For i = 1 To ws.Count

        If ws(i).Name = "Result" Then

            Dim str As String

            str = "Result" & Format(DateTime.Now, "yyyy-MM-dd hh-mm-ss")

            ws(i).Name = str

        End If

    Next i

    Worksheets.Add Count:=1, Before:=Sheets(1)

    Sheets(1).Name = "Result"

End Function

---

Private Function AppendData(ws As Sheets)

    Dim result As Worksheet

    Dim i As Long    ' sheet count

    Dim j As Long    ' row count in from sheet

    Dim k As Long    ' row count for result sheet

    Set result = ws(1)

    k = 1

   

    For i = 2 To ws.Count

        For j = 1 To ws(i).Rows.Count

            If WorksheetFunction.CountA(ws(i).Rows(j)) <> 0 Then

                ws(i).Rows(j).EntireRow.Copy result.Rows(k)

                k = k + 1

            Else

                Exit For

            End If

        Next j

    Next i

End Function

5.  Save it, remember you can not save permanently macro in .XLS or .XLSX file, to save the macro permanently you have to save the file as '.XLSM' called macro enable excel file.

6.  Now to run the macro again press the ALT+F8, select the macro with the name Merge and click to run the macro.


So can download the sample file from here

Happy Coding.. :)