Merge excel workbooks from folder into new open workbook

October 4, 2022

This Macro I have continues to merge the files from my folder into the current open workbook. I am trying to open a new workbook and have it merge into the new open workbook. I am not sure where I need to activate the newly opened/ added workbook in order for the merge to take place in that file.

Sub MergeWorkbooks()


Application.DisplayAlerts = False
Workbooks.Add
ActiveWorkbook.SaveAs FileName:="C:\ ....\Merged Files.xlsx"

Dim FolderPath As String
Dim Filename As String
Dim Sheet As Worksheet
Application.ScreenUpdating = False
FolderPath = "<Folder destination>"
Filename = Dir(FolderPath & "*.xls*")
 
workbooks("Merged Files.xlsx").Activate

Do While Filename <> ""
 Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True
 For Each Sheet In ActiveWorkbook.Sheets
 Sheet.Copy After:=ThisWorkbook.Sheets(1)
 Next Sheet
 Workbooks(Filename).Close
 Filename = Dir()
Loop
Application.ScreenUpdating = True
End Sub

>Solution :

Since Workbooks.Add and Workbooks.Open return a reference to the newly added or opened workbook, use two Workbook variables:

Dim mergedWb As Workbook
Set mergedWb = Workbooks.Add()

mergedWb.SaveAs FileName:="C:\ ....\Merged Files.xlsx"

...

Do While Filename <> ""
    Dim wb As Workbook
    Set wb = Workbooks.Open(Filename:=FolderPath & Filename, ReadOnly:=True)
 
    For Each Sheet In wb.Sheets
       Sheet.Copy After:=mergedWb.Sheets(1)
    Next Sheet
 
    wb.Close
    Filename = Dir()
Loop