Excel - Merge several worksheets into a single one


I use Excel to store information about several products and have a different worksheet for each range. How to merge (sequentially combine) all these worksheets into single page? All worksheets the same number of columns, with the same title (in the top row).
How to proceed?


Assuming that:
1. A sheet titled Consolidated can be created and deleted by macro
2. Headers are on row 1 on all sheets and is common for all
3. All sheets are to be copied
Sub CombineSheets()  
Dim lConRow As Long  
Dim Sheet As Variant  
Dim sConsolidatedSheet As String  
Dim lSheetRow As Long  
Dim sLastCol As String  

    sConsolidatedSheet = "Consolidated"  

    On Error Resume Next  
    On Error GoTo 0  
    ActiveSheet.Name = sConsolidatedSheet  
    For Each Sheet In Sheets  
        If Sheet.Name = sConsolidatedSheet Then GoTo Next_Sheet  
        If sLastCol = "" Then  
            sLastCol = Sheet.Cells(1, Columns.Count).End(xlToLeft).Address  
            Sheets(sConsolidatedSheet).Range("1:1") = Sheet.Range("1:1").Value  
            lConRow = 1  
        End If  
        lSheetRow = 0  
        On Error Resume Next  
            lSheetRow = Sheet.Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row  
        On Error GoTo 0  
        If (lSheetRow > 1) Then  
            Sheets(sConsolidatedSheet).Range(lConRow + 1 & ":" & lSheetRow + lConRow - 1) = Sheet.Range("2:" & lSheetRow).Value  
            lConRow = Sheets(sConsolidatedSheet).Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row  
        End If  
End Sub

Thanks to rizvisa1 for this tip.
Published by deri58. Latest update on March 20, 2013 at 10:44 AM by deri58.
This document, titled "Excel - Merge several worksheets into a single one," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).
Excel - Populate multiple sheets based on data entered in a main sheet
Excel - Concatenate multiple tables