macro to merge worksheets in excel

try this:
experts-xchange-example-b.xlsm

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

Select allOpen in new window

Where to copy the code
EE-Example-Combine-WorkSheets.xlsm

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

sorry, you mean Roy's suggestion?

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

sorry - file is now attached
experts-exchange-example-C.xls

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

Select allOpen in new window

For details refer to the attached workbook.
experts-xchange-example.xls

My example works fine. Can you attach your workbook so that I can see what is different.

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

Select allOpen in new window

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.

@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?

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.

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

>>It stops at the Master line

Stopped at this line?

Set MWS = Sheets("Master")

it means you need to have a worksheet named "Master".