VBA CODE

Question

Hi i have use the below code to merge multiple workbook in a single workbook but the problem with this code does not suite what i need as it copies all sheets in each workbook that i want to merge.

But in my need i just need specific sheet to merge in each workbook.

Help please!!!!

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

Answer 1

Figure out already.

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

            Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)

             countFiles = countFiles + 1

            countSheets = wbkSrcBook.Sheets.Count 'total sheets in this workbook

For Each wksCurSheet In wbkSrcBook.Sheets

'last sheet got an index equal to countSheets.

'the sheet before the last one will be then countSheets-1

If wksCurSheet.Name = "report" Then 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 " & countFiles & " worksheets", Title:="Merge Excel files"

        End If

    Else

        MsgBox "No files selected", Title:="Merge Excel files"

    End If

End Sub