combine all sheets data into one sheets

combinedata.vbs

Option Explicit

 '---------------------------------------------------------------------------------------
 ' Procedure : Combinedata
 ' Author    : Roy Cox
 ' Website   : www.excel-it.com
 ' Date      : 10/10/2010
 ' Amended   : 06/04/2013
 ' 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 = "Servers" '<-destination sheet here
    Const sRw As Long = 1 'data starts in Row10

    Cnt = 1

    Set wsMain = Worksheets(ShtName)
    wsMain.Cells.Clear

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> wsMain.Name Then
            Set DataRng = ws.Cells(sRw, 1).CurrentRegion
            If Cnt = 1 Then '<- first run
                DataRng.Copy wsMain.Cells(sRw, 1)
                Cnt = Cnt + 1 '<- increase cnt to prevent copying headers subsequently
            Else: Rw = wsMain.Cells(wsMain.Rows.Count, 1).End(xlUp).Row + 1
                 'don't copy header rows
                With DataRng
                    .Offset(1, 0).Resize(.Rows.Count - 1, _
                    .Columns.Count).Copy wsMain.Cells(Rw, 1)
                End With
            End If
        End If
    Next ws

End Sub