Excel - Merge several worksheets into a single one

December 2016




Issue


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?

Solution


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  
      
        Sheets(sConsolidatedSheet).Delete  
      
    On Error GoTo 0  
      
    Sheets.Add  
    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  
          
Next_Sheet:  
      
    Next  
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.