Merge excel files with multiple sheets (with same name) in one file with VBA

[komhs]

Hi to all and thanks in advance for your effort to help me

I have a problem. I am a basic user of VBA and it is difficult for me to find a solution on the following issue:

I have two excel files than contain same structure data in columns.

The first xlslx file is the master file with the name "Practice". Lets say that has sheets named "TODO", "DONE", "FUTURE", "PAST".
The second xlslx file is the child with the name "Practice55". Lets say that has sheets named "TODO", "FUTURE", "TODAY", "TOMORROW"

So, I want to keep an excel file (I don't care if it is new or the new data will be added to the existing one) which will keep the master's excel name, which means "Practice" by:
A) keeping all it's sheets ("TODO", "DONE", "FUTURE", "PAST")
B) adding the unique (not common named) sheets that "Practice55" has ("TODAY", "TOMORROW") and
C) every time that finds common named sheets (in this particular situation "TODO", "FUTURE"), to add the data of the child file's sheet ("Practice55") into the last empty row of the master file -in this particular situation "Practice".

At the end of the day the new (or not new) file will have the name "Practice" and it will contain the following sheets:
"TODO" (by adding and the data of Practice55's "TODO")
"DONE"
"FUTURE" (by adding and the data of Practice55's "FUTURE")
"PAST"
"TODAY"
"TOMORROW"

Can anyone help me on tis please?

Thanks in advance

 

[mumps]

Make sure that both workbooks are open. Place this macro in the Master file and run it from there. Change the workbook name (in red) to suit your needs.

Sub CopySheets()
    Application.ScreenUpdating = False
    Dim desWB As Workbook, srcWB As Workbook, ws As Worksheet
    Set desWB = ThisWorkbook
    Set srcWB = Workbooks("Practice55.xlsx")
    With CreateObject("scripting.dictionary")
        For Each ws In desWB.Sheets
            If Not .exists(ws.Name) Then
                .Add ws.Name, Nothing
            End If
        Next ws
        For Each ws In srcWB.Sheets
            If Not .exists(ws.Name) Then
                srcWB.Sheets(ws.Name).Copy After:=desWB.Sheets(desWB.Sheets.Count)
            Else
                With desWB.Sheets(ws.Name)
                    srcWB.Sheets(ws.Name).UsedRange.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                End With
            End If
        Next ws
    End With
    Application.ScreenUpdating = True
End Sub

 

[komhs]

Make sure that both workbooks are open. Place this macro in the Master file and run it from there. Change the workbook name (in red) to suit your needs.

Sub CopySheets()
    Application.ScreenUpdating = False
    Dim desWB As Workbook, srcWB As Workbook, ws As Worksheet
    Set desWB = ThisWorkbook
    Set srcWB = Workbooks("Practice55.xlsx")
    With CreateObject("scripting.dictionary")
        For Each ws In desWB.Sheets
            If Not .exists(ws.Name) Then
                .Add ws.Name, Nothing
            End If
        Next ws
        For Each ws In srcWB.Sheets
            If Not .exists(ws.Name) Then
                srcWB.Sheets(ws.Name).Copy After:=desWB.Sheets(desWB.Sheets.Count)
            Else
                With desWB.Sheets(ws.Name)
                    srcWB.Sheets(ws.Name).UsedRange.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                End With
            End If
        Next ws
    End With
    Application.ScreenUpdating = True
End Sub

First of all thank you so much for your effort!

I have done all you said (at least I think I did) but when I run the process, in the master File, it has created second sheets from the common named sheets. So If I had common named sheets "TODO" and "DONE", It has added "TODO" (2), "DONE" (2)" sheets, instead of filling in child's content to master sheet.

 

[mumps]

I tested the macro on two dummy workbooks and it worked properly.
Could upload copies of your two files (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here.

 

[komhs]

I tested the macro on two dummy workbooks and it worked properly.
Could upload copies of your two files (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here.

I have done that twice and the result was the same. I have a suspicion. Maybe has to do with the fact that common sheet names have the same name, but one letter is capital. Anyway. Please check if this is the problem or I am doing something wrong. Here you are...
(the master file is: "Final Time")

Loading Google Sheets, Loading Google Sheets

 

[komhs]

I have done that twice and the result was the same. I have a suspicion. Maybe has to do with the fact that common sheet names have the same name, but one letter is capital. Anyway. Please check if this is the problem or I am doing something wrong. Here you are...
(the master file is: "Final Time")

Loading Google Sheets, Loading Google Sheets

I am almost 100% sure that the upper and lower case is the problem. Can you add please into the code this particularity? To ignore the case sensitivity?
Thanks in advance

 

[Fluff]

Try adding this line as shown

    With CreateObject("scripting.dictionary")
        .CompareMode = 1
        For Each ws In desWB.Sheets

 

[komhs]

Try adding this line as shown

    With CreateObject("scripting.dictionary")
        .CompareMode = 1
        For Each ws In desWB.Sheets

I think that everything went well.

Thank you so much for your help!!!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[mumps]

Welcome to the Forum. You would have a better chance of receiving responses if you started your own new thread. Include a link to this thread if you feel it is useful.