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!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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
11,727
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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
11,727
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows

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
11,727
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,305
Messages
5,836,534
Members
430,437
Latest member
Emilycr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top