merge excel sheets to one - using vba - code revealed

1. Open Blank Excel File and save as .xlsm workbook.(.xlsm is for macro enabled workbook)

2. in the same location of your workbook create a folder as “Import” , where you will paste files that needs to be merged. 

3.  go to excel file and click on Developer tab and insert Active X Control > Button (if you dont see developer tab then you can get it by going to options > customize ribbon > Check Developer tab checkbox) 

4. Right click and choose properties in the Excel - Active X button and Name button as Btn_ImportFiles under properties.

5. Double Click the button and Paste following code between Private Sub Btn_ImportFiles_Click() and EndSub

‘############################################################

Dim sMyDir, default_path, sDocName As String

Dim LastRow As Long

Dim LastCol As Integer

Dim HeaderCopied_Flag As Boolean

Dim sh As Excel.Worksheet

Dim SourceWB As Excel.Workbook

Dim DestinationWB As Excel.Workbook

Application.ScreenUpdating = False

'************************************************************************************

default_path = ThisWorkbook.Path & "\Import"   'PATH OF YOUR IMPORT FILES

sMyDir = (default_path & "\")

sDocName = Dir(sMyDir)

If sDocName = "" Then

    MsgBox "No Files found", vbCritical

    Exit Sub

End If

Set DestinationWB = Workbooks.Add

While sDocName <> ""

    If sDocName Like "*.xls*" Then

    Set SourceWB = Workbooks.Open(sMyDir & sDocName, ReadOnly:=True)

            For Each sh In SourceWB.Sheets

        

               With sh

                   LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

                   LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

                   .Range(.Cells(VBA.IIf(HeaderCopied_Flag = False, 1, 2), 1), .Cells(LastRow, LastCol)).Copy

               End With

    

               With DestinationWB.Sheets(1)

                   LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + VBA.IIf(HeaderCopied_Flag = False, 0, 1)

                   .Range("A" & LastRow).PasteSpecial xlPasteValues

               End With

               

               Application.CutCopyMode = False

               '******************************************************************************************

               HeaderCopied_Flag = True ' CHANGE THIS TO FALSE IF YOU WANT HEADER FOR EACH FILE

            

            Next sh

         Workbooks(sDocName).Close

    End If

 sDocName = Dir()

Wend

  

Application.ScreenUpdating = True

MsgBox "Done."

‘############################################################

 6. go back to excel developer tab and choose Exit Design Mode and Click on the active X button to run the code.

for more information watch video above.