Macro to copy all sheets to one master sheet

[italal]

Usually I find my answers searching this awesome forum, but this one seems a little bit complicated.

I have different Excel files with different number of sheets, what I would like to have is a Macro that does the following:
1) Unmerge any merged cell*
2) Copy all sheets in the workbook
3) Paste these sheets to a Master sheet

What makes it more complicated for me is that each sheet has different number of rows, so I hope the Macro would address that.

* This step is to allow pasting the data to the master sheet, since most of the sheets have some merged cells.


Thanks in Advance!

 

[italal]

To clarify, I'm interested to apply the macro to each workbook separately.

 

[mumps]

Are all the workbooks open or does the macro have to open them? If the macro has to open them, what is the full path to the folder containing the source files. What is their extension (xlsx, xlsm)? Are they the only files in that folder? Do you want all the source sheets to be copied to one sheet in the Master or each to their own separate sheet?

 

[italal]

Hello mumps,
I will open each workbook manually, so no need for the macro to locate them.
I want all sourse sheets in a workbook to be copied to one Master sheet in that workbook.

To clarify, I will apply the macro to one workbook at a time.

 

[mumps]

Place this macro in a standard module in the Master workbook. Make sure it has a sheet named "Master". Open the first source workbook. Make sure that the Master and the source workbook are the only workbooks open. Run the macro.

Sub CopySheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, wb As Workbook, desWS As Worksheet
    Set desWS = ThisWorkbook.Sheets("[COLOR="#FF0000"]Master[/COLOR]")
    For Each wb In Workbooks
        If wb.Name <> ThisWorkbook.Name Then
            For Each ws In wb.Sheets
                ws.Cells.UnMerge
                ws.UsedRange.Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
            Next ws
        End If
    Next wb
    Application.ScreenUpdating = True
End Sub

 

[italal]

I did all the steps, however the macro only copied the last 3 sheets in the opened workbook. Also I noticed that some of the data of those 3 sheets were missing.

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual files which include any macros you are currently using. Perhaps you could upload a copy of your Master file and at a copy of at least one source file 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. If the workbook contains confidential information, you could replace it with generic data.

 

[italal]

I checked with my boss, unfortunately I won't be able to share the files even after replacing the data. I will see what can I do.

Many thanks for your help mumps.