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

D

dilshod_k

Board Regular
Joined
Feb 13, 2018
Messages
79
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
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
 
Upvote 0
JLGWhiz said:
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
Click to expand...
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Solution
JLGWhiz said:
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
Click to expand...
Thanks a lot! It does the job perfectly well! Appreciate it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
easy fix just need first sheet only in each file on this copy all first sheets into one workbook
Replies
2
Views
147
jordanburch
J
M
  • Solved
Merging Workbooks side by side instead of top to bottom
Replies
2
Views
262
Mr_Phil
M
P
  • Question
VBA to copy multiple worksheets and either rename sheet with file name or add column with filename
Replies
5
Views
1K
I_know_nuffin
I
A
  • Question
Automatically name active worksheet, and name active cell based on active worksheet
Replies
4
Views
419
alxn
A
G
  • Question
Combining Data from Multiple Workbooks
Replies
2
Views
978
pjmorris
P

Forum statistics

Threads
1,214,650
Messages
6,120,736
Members
448,988
Latest member
BB_Unlv

Share this page

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
Back
Top