• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combine 2 Workbooks

herofox

Member
try to use this code
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
                    countSheets = countSheets + 1
                    wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
                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
 

herofox

Member
and there's another code
Code:
Sub GetSheets()
'Update ExcelJunction.com
    
    Path = ""
    Filename = Dir(Path & "*.xlsx")
    Do While Filename <> ""
    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
    
    For Each Workbook In Workbooks
       If Workbook.Name <> ThisWorkbook.Name Then
    Workbook.Worksheets(1).Copy Before:=ThisWorkbook.Sheets(1)
    ActiveSheet.Name = Workbook.Name
    End If
    Next
    Set Workbook = Nothing
    Workbooks(Filename).Close
    Filename = Dir()
    Loop
End Sub
this video for it
 

Tech56

Member
Hello,

I tried the first code thanks. However in the named manager there are so many references to the previous workbook.
 

Tech56

Member
Thanks but I copied all worksheets from both workbooks to a new workbook. I then painstakingly searched for all formulas on each sheet and removed the references to the old workbooks. I also did that for the Named Manager for each formula reference. I am pretty sure there aren't any more links. Of course time will tell.
 
Top