[Solved] Macro VBA Help in copying specific worksheets [closed]

by


All worksheets in a Workbook got an Index property. Index 1 means first sheet, index 2 second one, and so on. So the last sheet will have an Index equal to Sheets.Count. Knowing this, try replacing this part:

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

And use this code:

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.Index = countSheets Or wksCurSheet.Index = (countSheets - 1) Then wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
            Next
            wbkSrcBook.Close SaveChanges:=False
        Next

Also, replace line:

MsgBox "Procesed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"

With:

MsgBox "Procesed " & countFiles & " files" & vbCrLf & "Merged 2 worksheets", Title:="Merge Excel files"

6

solved Macro VBA Help in copying specific worksheets [closed]