"If" condition in merging multiple workbooks with multiple sheets.

dilshod_k

Board Regular
Joined
Feb 13, 2018
Messages
76
Hello everyone.
I 've multiple workbooks with similar structure. Apart from number of sheets with random names all of them have three sheets with names "Mastersheet", "Control" and "Response". All files in one directory. I found VBA Macro to merge multiple workbooks but I could not incorporate "If" condition to avoid merging sheets with names "Mastersheet", "Control" and "Response". It gives error message. I'd be grateful for help with this macro.

VBA Code:
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)
                
                If wksCurSheet.Name <> "MasterSheet" And wksCurSheet.Name <> "Control" And wksCurSheet.Name <> "Response"  Then
 
                For Each wksCurSheet In wbkSrcBook.Sheets
                    countSheets = countSheets + 1
                    wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
                Next
                
                End If
                
                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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Try moving your If statement to inside the For loop. You cannot use the wksCurSheet variable until it is initialized by the 'For Each'.

VBA Code:
For Each wksCurSheet In wbkSrcBook.Sheets
    If wksCurSheet.Name <> "MasterSheet" And wksCurSheet.Name <> "Control" And wksCurSheet.Name <> "Response" Then
        countSheets = countSheets + 1
        wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
    End If
Next
 

dilshod_k

Board Regular
Joined
Feb 13, 2018
Messages
76
Try moving your If statement to inside the For loop. You cannot use the wksCurSheet variable until it is initialized by the 'For Each'.

VBA Code:
For Each wksCurSheet In wbkSrcBook.Sheets
    If wksCurSheet.Name <> "MasterSheet" And wksCurSheet.Name <> "Control" And wksCurSheet.Name <> "Response" Then
        countSheets = countSheets + 1
        wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
    End If
Next
Tried to modify code as it follows below. Now it gives error message after the second "Next" command - "Next without For". I tryed to eliminate second "Next" it did not work either.
VBA Code:
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 wksCurSheet In wbkSrcBook.Sheets
                If wksCurSheet.Name <> "MasterSheet" And wksCurSheet.Name <> "Control" And wksCurSheet.Name <> "Response" Then
                    countSheets = countSheets + 1
                    wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
                End If
            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
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You are not using the same code you originally posted. You had two For Each loops in that code. Only the inner loop needed to be changed.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

This is how it should look.

VBA Code:
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
                        If wksCurSheet.Name <> "MasterSheet" And wksCurSheet.Name <> "Control" And wksCurSheet.Name <> "Response" Then
                            countSheets = countSheets + 1
                            wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
                        End If
                    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
 
Solution

dilshod_k

Board Regular
Joined
Feb 13, 2018
Messages
76
This is how it should look.

VBA Code:
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
                        If wksCurSheet.Name <> "MasterSheet" And wksCurSheet.Name <> "Control" And wksCurSheet.Name <> "Response" Then
                            countSheets = countSheets + 1
                            wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
                        End If
                    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
Thanks a lot! It does the job perfectly well! Appreciate it.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You're welcome,
regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,122,752
Messages
5,597,921
Members
414,190
Latest member
PuzzlerUK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top