Friday, 22 March 2013

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
    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
        ActiveWorkbook.Close SaveChanges:=False
     SrcFileName = Dir() 'Allows to Go to Next File in a Directory

    TargetFile.Close SaveChanges:=True

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


<<< Keep on Following My Blog >>>

<<< Excel your Skills in Excel VBA Macros >>>



  1. Replies
    1. Hi User,
      The Macro will open the Workbooks Automatically.. then closes once the Task got finished.


Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts