macro to merge worksheets in excel

there are 5 worksheets in this file, but it may many more.
the objective is to merge all spreadsheets into a new master worksheet.
I am seeking a macro that I can copy into other files.
experts-xchange-example.xls
gregfthompsonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Roy CoxGroup Finance ManagerCommented:
This is code that I have used for years, place this code in a Standard Module


Where to copy the code
Option Explicit


'---------------------------------------------------------------------------------------
' Module    : Module1
' DateTime  : 09/05/2007 08:43
' Author    : Roy Cox (royUK)
' Website   : www.excel-it.com for more examples and Excel Consulting
' Purpose   : combine data from multiple sheets to one
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

'---------------------------------------------------------------------------------------


Private Sub CommandButton1_Click()

    Dim ws As Worksheet
    Dim DataRng As Range
    Dim Rw As Long

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> ActiveSheet.Name Then
            Rw = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
            If Rw = 1 Then
                '/// we need to copy the header row
                Set DataRng = ws.Cells(1, 1).CurrentRegion
                DataRng.Copy ActiveSheet.Cells(Rw, 1)
            Else: Rw = Rw + 1
                ''///don't copy header rows
                DataRng.Offset(1, 0).Resize(DataRng.Rows.Count - 1, _
                                            DataRng.Columns.Count).Copy ActiveSheet.Cells(Rw, 1)
            End If
        End If
    Next ws
    ActiveSheet.Columns.AutoFit
End Sub

Open in new window






Where to copy the code
EE-Example-Combine-WorkSheets.xlsm
gregfthompsonAuthor Commented:
Thanks Ryan,

It stopped with a Microsoft Visual Basic window:  Run-time error '91':  Object variable or With Block variable not set :

In de-bug mode this is the highlighted script:

DataRng.Offset(1, 0).Resize(DataRng.Rows.Count - 1, _
                                            DataRng.Columns.Count).Copy ActiveSheet.Cells(Rw, 1)

What did I do wrong?

Thanks,

Greg
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
sorry, you mean Roy's suggestion?
gregfthompsonAuthor Commented:
Sorry Ryan,

That query was meant for Roy.

I also could not get your example to work in a different file.

Can you make it work in the amended attached example. So that a new master worksheet is created containing contents of all other worksheets?

Thanks,

Greg
gregfthompsonAuthor Commented:
sorry - file is now attached
experts-exchange-example-C.xls
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try the below code to see if you get the desired output.

I have named the first sheet as Master. You may change it in the code as per your requirement.
Please click the button Merge Sheets on the Master sheet to run the code.

Here is the code.
Option Explicit

Sub MergeSheetsIntoMaster()
Dim MWS As Worksheet, WS As Worksheet
Dim cnt As Long

Application.ScreenUpdating = False

Set MWS = Sheets("Master")      'Master is your Master Sheet, change it as per your requirement.
MWS.Cells.Clear
For Each WS In Worksheets
    If WS.Name <> "Master" Then
        cnt = cnt + 1
        If cnt = 1 Then
            If WS.UsedRange.Cells.Count > 1 Then WS.UsedRange.Copy MWS.Range("A1")
        Else
            If WS.UsedRange.Cells.Count > 1 Then WS.UsedRange.Offset(1).Copy MWS.Range("A" & Rows.Count).End(3)(2)
        End If
    End If
Next WS
If MWS.Range("A1").Value = "" Then MWS.Rows(1).Delete
MWS.Cells.WrapText = False
MWS.Rows.Columns.AutoFit
MWS.Columns.AutoFit
Application.ScreenUpdating = True
MsgBox "Finished.", vbInformation
End Sub

Open in new window


For details refer to the attached workbook.
experts-xchange-example.xls
Roy CoxGroup Finance ManagerCommented:
My example works fine. Can you attach your workbook so that I can see what is different.
Roy CoxGroup Finance ManagerCommented:
I've just noticed that I forgot to mention adding a button, to run as a module change to
Option Explicit


'---------------------------------------------------------------------------------------
' Module    : Module1
' DateTime  : 09/05/2007 08:43
' Author    : Roy Cox (royUK)
' Website   : www.excel-it.com for more examples and Excel Consulting
' Purpose   : combine data from multiple sheets to one
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

'---------------------------------------------------------------------------------------


Private Sub Mergesheets()

    Dim ws As Worksheet
    Dim DataRng As Range
    Dim Rw As Long

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> ActiveSheet.Name Then
            Rw = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
            If Rw = 1 Then
                '/// we need to copy the header row
                Set DataRng = ws.Cells(1, 1).CurrentRegion
                DataRng.Copy ActiveSheet.Cells(Rw, 1)
            Else: Rw = Rw + 1
                ''///don't copy header rows
                DataRng.Offset(1, 0).Resize(DataRng.Rows.Count - 1, _
                                            DataRng.Columns.Count).Copy ActiveSheet.Cells(Rw, 1)
            End If
        End If
    Next ws
    ActiveSheet.Columns.AutoFit
End Sub

Open in new window

gregfthompsonAuthor Commented:
Thanks to all, but I would prefer to simply copy the script into a module and then run the macro. Copying the button adds another few steps that will have to be repeated for about 140 files.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
@Greg

My example should work fine too. Tested before uploaded. It will auto-populate the first worksheet (as Master) with the data from the following worksheets.

>>Can you make it work in the amended attached example. So that a new master worksheet is created containing contents of all other worksheets?

So you want a new master worksheet to be created every time the merge script is run?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The workbook I attached contains the code on Module1, you can run the code from there also.
I just added the button in order to test the code. If you don't like the button added on Master Sheet, just delete that button. That's it.
gregfthompsonAuthor Commented:
I don't know what I'm doing wrong but I copied the script from sktneer into a different file and it won't run.

file is attached with the script included.

It stops at the Master line
experts-exchange-example-2-sktneer.xls
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>It stops at the Master line

Stopped at this line?

Set MWS = Sheets("Master")

it means you need to have a worksheet named "Master".
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
It didn't work because you didn't have a sheet named Master to have data from all the sheets.
So the tweaked code will add a sheet named "Master" if it doesn't exist already.

Try the below code and see if it works now...
Sub MergeSheetsIntoMaster()
Dim MWS As Worksheet, WS As Worksheet
Dim cnt As Long

Application.ScreenUpdating = False
On Error Resume Next
Set MWS = Sheets("Master")
MWS.Cells.Clear
If Err <> 0 Then
    Worksheets.Add(before:=Worksheets(1)).Name = "Master"
End If

Set MWS = Sheets("Master")      'Master is your Master Sheet, change it as per your requirement.

For Each WS In Worksheets
    If WS.Name <> "Master" Then
        cnt = cnt + 1
        If cnt = 1 Then
            If WS.UsedRange.Cells.Count > 1 Then WS.UsedRange.Copy MWS.Range("A1")
        Else
            If WS.UsedRange.Cells.Count > 1 Then WS.UsedRange.Offset(1).Copy MWS.Range("A" & Rows.Count).End(3)(2)
        End If
    End If
Next WS
If MWS.Range("A1").Value = "" Then MWS.Rows(1).Delete
MWS.Cells.WrapText = False
MWS.Rows.Columns.AutoFit
MWS.Columns.AutoFit
Application.ScreenUpdating = True
MsgBox "Finished.", vbInformation
End Sub

Open in new window

experts-exchange-example-2-sktneer.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.