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.
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
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
SheetIndex = SheetIndex + 1
SrcFileName = Dir() 'Allows to Go to Next File in a Directory
Application.EnableEvents = True
Application.DisplayAlerts = True
MsgBox "All Wokbooks with All Sheets Successfllly Exported To Target File"