Macro to copy all sheets to one master sheet

italal

New Member
Joined
Mar 15, 2019
Messages
5
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!
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

italal

New Member
Joined
Mar 15, 2019
Messages
5
To clarify, I'm interested to apply the macro to each workbook separately.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,589
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

New Member
Joined
Mar 15, 2019
Messages
5
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

Well-known Member
Joined
Apr 11, 2012
Messages
9,589

ADVERTISEMENT

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.
Code:
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

New Member
Joined
Mar 15, 2019
Messages
5
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

Well-known Member
Joined
Apr 11, 2012
Messages
9,589
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

New Member
Joined
Mar 15, 2019
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,331
Messages
5,528,056
Members
409,799
Latest member
camronmartin

This Week's Hot Topics

Top