Merge worksheets

Here's some code that you can adapt

'---------------------------------------------------------------------------------------
' Module    : Module1
' DateTime  : 09/05/2007 08:43
' Author    : Roy Cox (royUK)
' Website   :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.

Option Explicit


'---------------------------------------------------------------------------------------
' Procedure : Combinedata
' Author    : Roy Cox
' Website   : www.excel-it.com
' Date      : 10/10/2010
' Purpose   : Combine data from all sheets to a master sheet
'---------------------------------------------------------------------------------------
'
Sub Combinedata()

    Dim ws As Worksheet
    Dim wsmain As Worksheet
    Dim DataRng As Range
    Dim Rw As Long
    Dim Cnt As Integer
    Const ShtName As String = "Master" '<-destination sheet here
    Cnt = 1

    Set wsmain = Worksheets(ShtName)
    wsmain.Cells.Clear
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> wsmain.Name Then
            If Cnt = 1 Then
                Set DataRng = ws.Cells(2, 1).CurrentRegion
                DataRng.copy wsmain.Cells(1, 1)
            Else: Rw = wsmain.Cells(Rows.Count, 1).End(xlUp).Row + 1
            MsgBox ws.Name & Rw
            Set DataRng = ws.Cells(2, 1).CurrentRegion
                '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
        Cnt = Cnt + 1
    Next ws

End Sub