How To Consolidate or Merge or Import Multiple Workbooks into One Workbook with Excel VBA Macro

Excel VBA Macro To Import or Export Multiple Workbooks into One Workbook
Excel VBA Macro To Merge Multiple Workbooks with Multiple Sheets into One Workbook
'This is a very user friendly macro that allows you Merge all work sheets of multiple workbooks from a folder/location in to a Newly created Workbook in other location.

Sub MergeAllWorkbooks()

    Dim SourcePath As String
    Dim SrcFileName As String
    
    Dim SourceFile As Workbook
    Dim TargetFile As Workbook
    Dim WS As Worksheet
   
    Dim SheetIndex As Integer
    Dim Export2File As String
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    
    SheetIndex = 1    
    Export2File = Format(Now(), " DD_MM_YYYY HH-MM AMPM ")

Target File location in to which files are to be Merge.....Change as you wish.......
    Workbooks.Add.SaveAs FileName:="D:\TPR\Merge\" & Export2File & ".xlsm ", FileFormat:=52
    'FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
    Set TargetFile = ActiveWorkbook
    
'Path From which Files are to be Merge........Change as you wish........
    SourcePath = "C:\Documents and Settings\Administrator\My Documents\"
    SrcFileName = Dir(SourcePath & "*.xls*")
    
    Do While SrcFileName <> ""
        Set SourceFile = Workbooks.Open(FolderPath & SrcFileName)
           
            For Each WS In SourceFile.Sheets 'Loop Through Each Worksheet
                WS.Copy Before:=TargetFile.Sheets(SheetIndex)
                SheetIndex = SheetIndex + 1
            Next WS
      
        SourceFile.Activate
        ActiveWorkbook.Close SaveChanges:=False
    
     SrcFileName = Dir() 'Allows to Go to Next File in a Directory
    Loop

    TargetFile.Close SaveChanges:=True
    
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    
    MsgBox "All Wokbooks with All Sheets Successfllly Exported To Target File"
       
End Sub

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------