Excel - Merge several worksheets into a single one

October 2016


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.

Related :

This document entitled « Excel - Merge several worksheets into a single one » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.