A few words of thanks would be greatly appreciated.

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.


A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
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 (ccm.net).