Scribd Logo
Upload

Welcome to Scribd!

  • 214 views

    Merge Multiple Excel Sheets

    Uploaded by

    Yamini Shinde
    This VBA macro allows users to merge multiple Excel files into one file. The macro copies all worksheets from selected Excel files and pastes them into a single target workbook. It first prompts the user to select multiple files to merge. It then opens each source file, copies all sheets, and pastes them after the last sheet in the destination file before closing the source file without saving. After processing all selected files, it provides a summary of the number of files merged and total worksheets copied.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Merge Multiple Excel Sheets

    Uploaded by

    Yamini Shinde
    214 views2 pages
    This VBA macro allows users to merge multiple Excel files into one file. The macro copies all worksheets from selected Excel files and pastes them into a single target workbook. It first prompts the user to select multiple files to merge. It then opens each source file, copies all sheets, and pastes them after the last sheet in the destination file before closing the source file without saving. After processing all selected files, it provides a summary of the number of files merged and total worksheets copied.

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This VBA macro allows users to merge multiple Excel files into one file. The macro copies all worksheets from selected Excel files and pastes them into a single target workbook. It first prompts the user to select multiple files to merge. It then opens each source file, copies all sheets, and pastes them after the last sheet in the destination file before closing the source file without saving. After processing all selected files, it provides a summary of the number of files merged and total worksheets copied.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    214 views2 pages

    Merge Multiple Excel Sheets

    Uploaded by

    Yamini Shinde
    This VBA macro allows users to merge multiple Excel files into one file. The macro copies all worksheets from selected Excel files and pastes them into a single target workbook. It first prompts the user to select multiple files to merge. It then opens each source file, copies all sheets, and pastes them after the last sheet in the destination file before closing the source file without saving. After processing all selected files, it provides a summary of the number of files merged and total worksheets copied.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 2

    How to merge Excel files with VBA

    If you have multiple Excel files that have to merged into one file, a faster
    way would be to automate the process with a VBA macro.
    Below you will find the VBA code that copies all sheets from all Excel
    files that you select into one workbook. This MergeExcelFiles macro is
    written by Alex, one of our best Excel gurus.
    Important note! The macro works with the following caveat - the files to
    be merged should not be open physically or in memory. In such a
    case, you will get a run-time error.
    ?
    Sub MergeExcelFiles()
        Dim fnameList, fnameCurFile As Variant
        Dim countFiles, countSheets As Integer
        Dim wksCurSheet As Worksheet
        Dim wbkCurBook, wbkSrcBook As Workbook
     
        fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel
    Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files
    to merge", MultiSelect:=True)
     
        If (vbBoolean <> VarType(fnameList)) Then
     
            If (UBound(fnameList) > 0) Then
                countFiles = 0
                countSheets = 0
     
                Application.ScreenUpdating = False
                Application.Calculation = xlCalculationManual
     
                Set wbkCurBook = ActiveWorkbook
     
                For Each fnameCurFile In fnameList
                    countFiles = countFiles + 1
     
                    Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
     
                    For Each wksCurSheet In wbkSrcBook.Sheets
                        countSheets = countSheets + 1
                        wksCurSheet.Copy
    after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
                    Next
     
                    wbkSrcBook.Close SaveChanges:=False
     
                Next
     
                Application.ScreenUpdating = True
                Application.Calculation = xlCalculationAutomatic
     
                MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " &
    countSheets & " worksheets", Title:="Merge Excel files"
            End If
     
        Else
            MsgBox "No files selected", Title:="Merge Excel files"
        End If
    End Sub

    You might also like