Adding multiple excel sheet and summing dynamic number of worksheets

Question

I have a code to merge multiple excel files into one workbook. However, after I merged the worksheets into another workbook, Those worksheets that has links to the previous workbooks are all broken. I wanted to sum all the worksheets in their specific fields in a "Total" worksheet in the new workbook as well.

Please help.

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 "Procesed " & 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

R35P3K7 R35P3K7 · Accepted Answer · 2020-07-17T07:38:37

assuming that all your sheets have same structure and you just want to sum them up then you can use built in function SUM in your Total sheet.

You can set range and then add formula to whole range at once (i have only 3 sheets 1,2,3 and total).

Dim xRng as Range, n as integer

n = Thisworkbook.Sheets.Count
Set xRng = ThisWorkbook.Sheets(n).Range("A2:D2")

xRng.FormulaR1C1 = "=SUM('" & ThisWorkbook.sheets(1).name & "':'" & ThisWorkbook.Sheets(n-1).name & "'!RC)"

Also it is good to use dynamic range using Last row and last column or named ranges...

I hope this helps atleast a bit :)

Adding multiple excel sheet and summing dynamic number of worksheets

1 Answers